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 is lot of way you can form select
queries that returns same result. For this article, I used Microsoft 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:
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:
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 shows the book titles and it also has the price of
each book title. Now, when we are querying the books from this table, we need
to display title, 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:
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>
4. Sub Queries
The query is actually referred by the Main query to decide
something. The 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:
Now let look at the Pubs Database. We know about the
Titles table. This table contains the books and it price along with other
information. The Sales table contains information about the sales
of the book titles along with some other information. Now let us see how do we
use sub-query to return the Book Titles that is not yet sold. This means that
we have a book in the titles table and no entry for that book is available in
the Sales Table. Have a look at the below Query:
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 condition of the Main
table. Note that the sub-query used the column returned by the main table as
T.Title_Id. When the sub Query returns null the “Not Exists” statement, which
is part of the main query, evaluates to true there by returning 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 is derived on the fly in the “FROM” clause of the query is
considered as a Table. Have a look at the below example:
The query takes the data from the Titles table of the Pubs
database. The internal select statement that looks like a sub-query is the
derived table here. The derived table provides the Title_id and 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. See how 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 removed from memory
when the Query execution is completed.
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:
In the above example, first we define 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. The mapping
of the CTE columns TitleId, Total is done by querying a table Sales in our
example. 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. I 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 ),
With <CTEName>(col1, col2, col3) As
( Select Query ),
With <CTEName>(col1, col2, col3) As
( Select Query )
