Some Select Query Techniques in SQL server.

1. Case...When…End

 

The Case..When is a 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 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 following query example:

Pic03.jpg

 

The portion of query marked with a blue box shows how the costing level is done. The case/when statement structure is making the decision in the “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 portion corresponds with the “When”. It is like:

 

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

 

2. 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 following picture:

 

Pic04a.jpg

 

Now to look at the Pubs Database. We know about the Titles table. This table contains the books and their 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 to use a sub-query to return the Book Titles that are 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 following query:

 

Pic04.jpg

 

In the above example, the Main query is on the Titles table and we want to return the titles, that is not making any sales. The sub-query is on the Sales table that 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 (that is part of the main query) evaluates to true thereby returning the row.


3. Derived Tables

 

In the previous example we saw a select statement embedded inside the Where portion of the main select statement. If a similar sub-query participates in the “From” clause of the Main select query then we call that aDerived 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 and is considered a Table. Have a look at the following example:

 

Pic05.jpg

 

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 a Derived Table. See how we used the Derived Table technique to display book titles information along with the 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 is removed from memory when the Query execution is completed.

 

4. Common Table Expressions (CTE)

 

We can obtain the same result given by the Derived Table technique by using the CTE technique. In the Common Table Expression technique, we define the table before starting the actual select query. Once the query is defined, it can be referred to in the subsequent select query multiple times. Now consider the following example:

 

Pic06.jpg

 

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

 

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 TotalSales made by a particular title. I can also use multiple CTEs in your query and in that case the definition of each CTE will be separated by a comma as in the following:

 

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

( Select Query ),

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

( Select Query ),

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

( Select Query )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s