Introducing Common Table Expressions in SQL Server 2005 - An example of a non-recursive common table expression

(Page 2 of 5 )

If we recollect the creation of temporary tables in previous versions of SQL Server, we can observe that there is some inconvenience (such as maintaining too many temporary tables).  We can remove some of the inconvenience by using CTE. Let me show you how with an example.

I am trying to design a CTE which displays a (temporary) result set that includes all employee names and their respective manager names.

Consider the following script.

Execute the above script in SQL Server Management Studio (using the "new query" option) and you should be able to view all employee names with the names of their respective bosses.  I shall explain the above script in the next section.

Explaining the example

In this section, I shall explain to you the script I presented in the previous section. I shall explain it part by part. Let us first start with the following:
 

The above statement retrieves several things. First, it simply retrieves the EmployeeID. It also retrieves the Name of the employee, by combining FirstName, MiddleName and LastName fields using the concatenation operator "+" and replacing the null value as an empty string using the "isnull" function. It retrieves the ManagerID by combining two different tables available in two different schemas (HumanResources.Employee, Person.Contact).

To combine the information from both tables we are using "INNER JOIN," concentrating on the column "ContactID" (from the tables Contact and Employee).  The entire result set retrieved by the above SELECT is identified with the alias "b."  To work with the same result set once again, I used the same SELECT statement as above but with the alias "a." 

To combine both "a" and "b", I am using LEFT OUTER JOIN as shown below.

The above statement retrieves EmployeeName, ManagerName from two tables (or result sets) with aliases "a" and "b." These two tables are combined using the "LEFT OUTER JOIN" command, which specifies that all rows from the left table (or result set) with matching rows from the right table (or result set) based on the condition "a.ManagerID = b.EmployeeID" need to be retrieved.  I used "LEFT OUTER JOIN" simply to include even the employees who don't have bosses!

The common table expression is created using the WITH statement followed by the CTE name. The name of the CTE will be followed by the column names representing the same columns retrieved by the SELECT statement defined within the WITH statement. 

That means, entire result set will be treated as a temporary table with the name "EmployeeManagers" and columns "EmployeeName" and "Manager."  Not only that, we need to select the columns from that CTE to show our output (which is also a part of the syntax defined within the WITH statement).  In fact, every WITH must be provided with the respective SELECT.  In this case, it looks like the following snippet:

Next: Can we group the output available from CTE? >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: Introducing Common Table Expressions in SQL Server 2005

· Hello guys! now you can learn CTEs in SQL Server 2005, which really boost up queries...    · If I try to use the CTE in 2 successive SELECTs, the first runs OK but the second...   >>> Post your comment now!