May 15, 2013

SQL 2005 - Sub-Queries, Derived Tables, and Common Table Expression (CTE)

1. Introduction

In this article, I will explain some useful select queries that come handy when you want to retrieve data from different tables. There are lot of way you can form select queries that returns same result. For this article, I used Microsoft's Pubs database. Let us explore the queries one by one.

2. Formatting the Query

Let us say you want to create mailing address of Authors from the Pubs database. The problem is that the data stored in the Authors table is not in the mailing address format. The data is spawn among multiple columns. Now, have a look at the below query:

Authors mailing address in different table column
Fig 1. Authors mailing address in different table column

The above query uses the operator + which joins two strings. We use this operator to concatenate special chars, two columns, a constant string etc.  Note that the space(2) function adds two blank spaces to the string formed. Char(13) and Char(10) together forms a Line Feed and Carriage Return. The result of executing the above query is show here:

Output of author's mailing address
Fig 2. Output of author's mailing address

This output looks like a mailing address, right?

3. Case...When...End

The "Case..When" is decision making structure in SQL Queries. To know how it can be used, let us consider an example. The Titles table from the Pubs database has the book titles and it also has the price of it. Now, let us say that when we are querying the books from this table, we need to display title and price of the book along with the cost suggestion like Low, Medium and High; based on some price range. Now look at the below query example:

Case...When...End Struct Example of SQL Server
Fig 3. Case...When...End Struct Example of SQL Server
The portion of query marked in blue box shows how the costing level is done. The case when statement structure is making the decision in “When” condition statement. When the condition evaluates to true the “Then” portion will be executed. For Example, when the price lies between 0 and 3, "Low" will be returned. The else potion comes in combination with “When”. It is like:

When (Condition) Then <true> Else <false>

The "End" keyword is used to tell that we are going to terminate the Case...When conditional construct.

4. Sub Queries

A "Sub-Query" is actually referred by the Main query to decide something. Sub-query can use the data returned by the Main query and in the mean time, the main query cannot have access to the columns retrieved by the sub queries. This is shown in the below picture:

Main Query Don't have access to data of Sub-Query
Fig 4. Main Query Don't have access to data of Sub-Query

Now let us look at the Pubs Database. We already know about the Titles table since we used it in the previous examples. This table contains the book title and it price along with other information. The Sales table contains information about the sales of the book titles along with some other information. 

Let us see, how do we use sub-query to return the Book Titles that is not yet sold even single piece. This means that we have a book in the titles table and no entry for that book in the Sales Table. Have a look at the below Query:

Sub-Query and Main Query Relation
Fig 5. Sub-Query and Main Query Relation

In the above example, Main query is on the Titles table and we want to return the titles, which is not making any sales. The sub Query is on the Sales table which participates in the where clause condition of the Main table. Note that the sub-query used the column returned by the main table as T.Title_Id. The sub-query is called by the main query for every row that it returns.

When the sub Query returns no record, the “Not Exists” statement, which is part of the where condition, evaluates to true there by there by signalling main query to return the row.

5. Derived Tables

In the previous example we saw a select statement embedded inside the Where portion of the main select statement. If similar sub query participates on the “From” clause of the Main select query, we call that as "Derived table". See, the table in the expected form does not exist, and hence the result-set derived on the fly. Have a look at the below example which shows how a Table is derived in the "From" clause of the query:

SQL Server Derived Table Example
Fig 6. SQL Server Derived Table Example

The query takes the data from the Titles table of the Pubs database. The internal select statement (marked as blue box) that looks like a sub-query is the derived table here. The derived table provides the Title_id and total sales made on that particular title by making an aggregate query on the Sales table. 

Note that we are joining the Titles table with the data returned from Derived Table based on the Title_id from the Titles table and Title_Id returned by the Derived Table. A query output that is kept in the FROM portion of the SELECT query makes it as Derived Table. Here, we used the derived table technique to display book titles information along with number of titles sold.

The derived Table is an "in-memory table" and SQL server wipes that from the memory when the query execution is completed. In our example the derived table is TS (Title_id, TotalSold).  This two-column table get removed from memory when the Query execution is over.

6. Common Table Expressions (CTE)

We can achieve the same result given by the Derived Table technique by using the "CTE" technique. In "Common Table Expression" technique, we define the table before starting the actual select query. Once the query is defined, it can be referred in the sub-sequent select query multiple times. Now consider the below example:

Common Table Expression Technique (CTE)
Fig 7. Common Table Expression Technique (CTE)

In the above example, first we defined the Common Table Expression as shown in the above depiction (indication mark 1). The common table Name is TotalByTitleId with two fields TitleId, Total in it. In our example, the mapping of CTE columns TitleId, Total is done by querying a table Sales. The returned result is kept as table construct TotalByTitleId.

The next select query uses this CTE construct as table. The select query retrieves the data from the Titles table and joins that with our CTE to get the TotalSales made by a particular title. You can also use multiple CTE in your query and in that case definition of each CTE will be separated by comma like below:

With <CTEName>(col1, col2, col3) As
( Select Query ),
<CTEName>(col1, col2, col3) As
( Select Query ),
<CTEName>(col1, col2, col3) As
( Select Query )


  1. My suggestion about formatting: don't do it. Formatting doesn't belong in queries, it has to be performed by the frontend. A query should process raw data and return raw data. String manipulation, concatenation, formatting and so on should be avoided in every query, view, Stored Procedure and so on.

  2. Agreed. If there are 1000 clients looking for the data and server does formatting... Got your point.

    But, we can still have formatting when it helps in filtering lot of data on server itself. I mean, a formatting that takes place in the where clause

  3. Nice article. I look forward to using some CTE's in the future.


Leave your comment(s) here.

Like this site? Tell it to your Friend :)