Archives for October 2012

In this video, I introduce the SQL WITH statement (also known as Common Table Expressions or CTE) and show you the basics of how it is used.

[sharebox5_no_text] [/sharebox5_no_text]

The SQL WITH Statement is called Common Table Expressions or CTE for short in SQL Server

The SQL WITH statement is used for 2 primary reasons:

1) To move Subqueries to make the SQL easier to read.

2) To do recursive queries in SQL

Today, I just want to talk about the subquery piece.

I first want to take a look at the Employee table in the SQL Training Online Simple Database.

select * from employee

To talk about the SQL WITH statement, I have to first talk about and show you a subquery.

select *
from
(
select * from employee
) a

So that is an example of a subquery.

But, we want to talk about the SQL WITH, which allows you to move the subquery up and make the SQL a lot easier to read.

Here is the same query using the WITH statement.

WITH cteEmployee (employee_number,employee_name,manager)
AS
(
select employee_number,employee_name,manager from employee
)
select *
from cteEmployee

You can see that we start with the WITH clause and then we can use any name we want to name our CTE. In this case, I use “cteEmployee”.

Then we need to specify the columns inside of parenthesis.

Next comes the AS clause.

And finally, we just SELECT from the cteEmployee table we created.

And, that’s it.

But, I want to take it a step further and join the cteEmployee CTE back to the Employee table and get the Manager’s name.

Here is an example of that.

WITH cteEmployee (employee_number,employee_name,manager)
AS
(
select employee_number,employee_name,manager from employee
)
select cte.employee_number
,cte.employee_name
,cte.manager
,e.employee_name as manager_name
from cteEmployee cte INNER JOIN employee e on cte.manager = e.employee_number

That’s it.

That is the basic introduction into the SQL WITH statement in SQL Server. Microsoft also has some good examples on this.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

The SQL Add (+), or the Addition operator is used to add to numbers and days to a date.  In this video, you will see how to use it inside of SQL Server Management Studio.

[sharebox5_no_text] [/sharebox5_no_text]

I first want to take a look at the Employee table in the SQL Training Online Simple Database.

select * from employee 

In particular, I want to look at the Salary and Commission columns.

We can add those two columns together by using the plus sign (+), or addition operator in SQL:

select salary 

,commision ,salary + commision 

from employee 

But, since there can be nulls in the commission column, we need to handle that with the isnull function:

select salary 

,isnull(commision,0) as commision 

,salary + isnull(commision,0) as total_compensation 

from employee 

Next, I want to show you how to add with a datetime.

When you use the SQL Add operator, SQL Server will actually add the number in days.

Here is an example:

select hire_date 

,hire_date + 2 as training_date 

from employee 

This example will add 2 days to the hire_date.  MSDN also esplains the SQL Add operator.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.