1. Introduction
In this example, we will explore some useful select queries that comes handy when we want to retrieve data from different tables. There many ways one can form select queries that returns the same result. For this article, we will use Microsoft’s Pubs database and explore Sub-Queries, Derived Tables and Common Table Expressions (CTE). We will explore these queries one by one.
2. Authors’ Mailing Address – Formatting the Result
Let us say we want to create a 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:
|
1 2 3 4 5 6 7 |
Select au_fname + space(2) + au_lname + char(13) + char(10) + address + char(13) + char(10) + city + char(13) + char(10) + State + ' - ' + zip + char(13) + char(10) + 'Phone - ' + phone + char(13) + char(10) + '========================================================' From authors as [Mailing Address]; |
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, the space(2) function adds two blank spaces to the string formed. The char(13) and char(10) together forms a ‘Line Feed’ and ‘Carriage Return’. The result of executing the above query is below:
This output looks like a mailing address, right? Note, we should change the output format in the management studio to see the formatted result. The below picture shows how to turn ON the ‘Result to Text’ option:
3. SQL Case..When..Then Decision Making
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 the 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:
|
1 2 3 4 5 6 7 8 |
Select Title_id, Title, Type, Price, Case When (Price <= 3 and Price > 0) Then 'Low' When (Price <= 15 and Price > 3) Then 'Medium' Else 'High' End As 'Cost' From titles; |
The portion of query marked in blue box shows how the costing level is done. The ‘case when then’ statement structure is making the decision in ‘When’ condition statement. When the condition evaluates to true, it will execute the ‘Then’ portion. For Example, when the price lies between 0 and 3, ‘Low’ will be returned from the ‘Then’ part. The ‘Else’ portion comes in combination with the ‘When’. It is like:
|
1 |
When (Condition) Then <true> Else <false> |
We use the ‘End’ keyword to tell the SQL Server that we are going to terminate the Case…When conditional construct.
4. Main Query and Sub-Query
Main query refers a ‘Sub-Query’ to decide something. Sub-query can use the data returned by the Main query and in the meantime, the main query cannot have access to the columns retrieved by the sub queries. This is shown in the below picture:
Now we will 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 its 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 a single piece. This means 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:
|
1 2 3 4 5 6 7 |
Select T.Title_id, T.Title, T.Type From Titles as T Where Not Exists ( Select * from Sales as S where S.Title_id = T.Title_id ) |
In the above example, Main query is on the ‘Titles’ table and we want to return the titles, which are not making any sales. The Sub-Query is on the ‘Sales’ table which takes part in the where clause condition of the Main table. Note, the sub-query uses the column returned by the main table as ‘T.Title_Id’. The Main Query calls the Sub-Query for every row that it returns. Means, for each row in ‘Titles’ table it will make use of the Sub-Query to test the where condition.
When the sub Query returns no record, the ‘Not Exists’ check, which is part of the where condition, returns true there by signalling main query to include the current row in the query result.
5. Derived Tables (Sub-Query in From Clause)
In the previous example we saw a select statement embedded inside the ‘Where Clause’ of the main select statement. If similar sub query takes part in the ‘From Clause’ of the Main select query, we call that as ‘Derived Table’. When we do not have a table in the with required field values, we derive the table on the fly from the existing tables. Have a look at the below example which shows how a Table is derived in the ‘From Clause’ of the query:
|
1 2 3 4 5 6 7 8 9 10 |
Select T.Title_Id, T.Title, T.Type, TS.TotalSold as Sold From Titles as T Inner Join ( Select Title_Id, Sum(Qty) as TotalSold From Sales Group By Title_Id ) As TS On T.Title_Id = TS.Title_Id; |
The query takes the data from the ‘Titles’ table of the ‘Pubs’ database. The internal select statement (marked as a blue box) which looks like a sub-query is the Derived Table here. It gives the Title_id and total sales made on that specific title by using the aggregate function on the Sales table.
Note, we are joining the Titles table with the data returned from the Derived Table. The Inner Join is based on the Title_id from the Titles table and Title_Id returned by the Derived Table. The query output in the FROM portion of the SELECT query makes it as Derived Table. In this Query Example, we used the derived table technique to display book titles along with Total Sales.
The derived Table is an ‘In-Memory Table’ and SQL server wipes that from the memory when the query execution is done. In our example, the derived table is TS (Title_id, Total Sold). This two-column table get removed from memory when the Query execution is over.
6. SQL CTE (Common Table Expressions)
We can reach the same result given by the Derived Table technique by using the ‘Common Table Expression’ approach. In CTE Style, we pull the table of data before starting the actual select query. Once the query is set, we can pick it in the subsequent select query multiple times. Now look at the below example:
|
1 2 3 4 5 6 7 8 9 10 11 |
With TotalByTitleId(TitleId, Total) as ( Select Title_id as TitleId, Sum(Qty) as Total From Sales Group By Title_Id ) Select T.Title_Id as TitleId, T.Title as TitleName, T.Type as Type, TAgg.Total as TotalSold From Titles T Inner Join TotalByTitleId TAgg On T.Title_id = TAgg.TitleId; |
In the above example, first we set the Common Table Expression as shown in the above depiction (sign Mark 1). The common table Name is TotalByTitleId with two fields TitleId, Total in it. In our example, the mapping of CTE columns TitleId and Total is done by querying the Sales table. We keep the pulled result as table construct, TotalByTitleId.
In the above example, the next select query uses this CTE construct as a table. The select query retrieves the data from the Titles table and joins that with our CTE to get the total sales made by a book title. One can also use multiple CTE in SQL scripts. In that case, the comma will separate each CTE like below:
|
1 2 3 4 5 6 |
With <CTEName>(col1, col2, col3) As ( Select Query ), <CTEName>(col1, col2, col3) As ( Select Query ), <CTEName>(col1, col2, col3) As ( Select Query ) |
That is all in this Example!
Categories: SQL Server
Tags: CTE, Derived Tables, Format SQL Result, Sub-Query
Do you like this Example? Please comment about it for others!!
This site uses Akismet to reduce spam. Learn how your comment data is processed.