Selecting Data – Part 4

Today, I am continuing our exploration of the SELECT reserved word by talking about sub-queries. A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. Sub-queries can be used anywhere an expression is allowed or can be nested inside another sub-query.

I will be using the AdventureWorks Sample database supplied by Microsoft during this talk.

One usage a sub-query is with comparison operators in the where clause. The example below returns all the sales people by name and id who are above average sellers.

Another usage of a sub-query is with the IN (inclusion) clause. In short, the sub-query or inner select statement, returns a list of values in which column or field has to match one value in the list. The example below shows all female sales agents.

The opposite of inclusion is exclusion. This condition is tested with the NOT IN (exclusion) clause. The example below shows all male sales agents.

The TSQL code below shows the number of distinct employees and total rate change records. This information is relevant to the existence and non-existence query examples coming up later.

We are going to use the EXIST clause to determine all employees who have had at least on rate change in the past. Existence is determined by the correlated sub-query that does not return a empty set, no rows.

The NOT EXIST clause determines if a empty set is returned by the sub-query. The next business problem is to find all the employees who have not had a rate change. One would think that add the word NOT to the TSQL statement above would solve our problem. However, it returns any non-matches including contacts who are not employees. We have to add an additional EXISTS clause to weed out all the non-employees.

Please note, queries that test exclusion or non existence always execute long than queries that test inclusion or existence. The reason for this is that a condition is meet when the first match is fond. On the other hand, all values have to be checked when testing for negativity.

Sub-queries can be used any where an expression can be used. The following example shows the list price of mountain bikes, the average prices of a mountain bike, and the difference from the average price for the given mountain bike. In short, two sub-queries are used as expressions.

The ANY, SOME and ALL keywords were added to the SQL Server in the 2005 edition. The following example finds all customers who have not been given a territory id which is assigned to a sales person. This includes all rows in which territory id is null.

The SOME is a great way to test for at least one condition is meet. The Adventure Works database has 32 mountain bikes in its product line. You have been tasked to find all Customer’s who have purchased a mountain bike and return their customer id. The query below does just that.

Another cool feature of subqueries is that they can be used with inserts, updates and deletes – data manipulation statements.

Let’s make believe that your manager wanted to have a fire sale on the mountain bikes to reduce stock. He has asked you to reduce the price by 50 percent. You begin the transaction, you update 32 records in the product table. He comes by your desk within minutes and tells you that he wants to wait 2 weeks before making this change. Therefore, you rollback the changes. The TSQL below executes the series of tasks.

Last but not least, I am going to end the discussion with the ALL keyword. I worked on this query for a while since real life examples lend themselves to using this keyword in a stored proceedure. For instance, the books on line example show a stored proceedure called DaysToBuild which takes an @OrderID and @NumberOfDays as input. If a order can be build in the number of days, a true statement is shown; otherwise a false statement is shown.

Since this is not a real example of using the ALL keyword in a select statement, I decided to introduce two powerful techniques. First, common table expressions (CTE’s) were added to SQL Server in the 2005 edition. They return back a dynamic view of the data. Second, a tally table is a table that contains numbers 1 to n. Armed with these two CTE’s, I am able to identify all the customers who have ordered 30 or more of mountain bikes.

Again, this example is artificial since we could get the answer by adding a HAVING clause to the mountain bikes SELECT statement to return the results with one query.

The lesson learn from today’s talk is that sub-queries are very powerful for selecting data. The IN, EXIST, ANY, SOME and ALL keywords allow a SQL developer to test for all types of conditions in the result set. Data manipulation queries such as INSERTS, UPDATES and DELETES can use sub-queries to pin point what rows to update. Like views, deterministic sub-queries can be updatable but have rules that need to be followed.