Advantage Developer Zone

 
 
 

Using Subqueries

Thursday, November 05, 2009

Subqueries are a very powerful and useful feature of the SQL standard. Subqueries are also referred to as Inner Queries or Nested Queries. One of the most common uses of a subquery is to filter data from one table based on the values in another table. However, they can be used for many other database operations as well. In this article we will examine several uses of subqueries.

Subquery Rules

A subquery can fall into one of three types; scalar, row and table. A scalar subquery returns a single value, a row subquery returns several columns from a single record and a table subquery returns several rows. When using a table subquery as an operand of a comparison expression (except EXIST), the subquery must return only a single column. Table subquery used in the FROM clause, used as values in an INSERT statement, or used as operand to the EXISTS operator may consist multiple columns.

Subqueries can be used in the WHERE and HAVING clause of SELECT statements. They can also be used in the WHERE clause of UPDATE and DELETE statements. Subqueries can also be used in an INSERT statement for example:

INSERT INTO Table (SELECT * FROM AnotherTable WHERE State = ‘ID’)
Finding Records

A common usage of a subquery is to find matches between two tables. Consider the following query:

SELECT * FROM Customer WHERE City IN (‘Boise’, ‘Nampa’, ‘Meridian’)

This query gets all the customers who live in the specified cities. This type of query can be very useful when you only need to specify a handful of cities. However, what if you want to search for all the cities in a particular county. This type of query would be much more difficult to do create manually. In this case we will use a subquery to get the results we want.

SELECT * FROM Customer Where City IN (SELECT City FROM ZipCodes
 WHERE State = ‘ID’ and County = ‘ADA’)

In this example the ZipCodes table contains information for all zip codes which includes the city, county and state for each. Therefore the subquery will return a list of all of the cities from Idaho in Ada county.

Locating Mismatches

Another common usage for subqueries is to locate records in one table that do not have a match in another table. Take a look at the following example:

SELECT LastName, FirstName, CompanyName FROM Customer WHERE CustomerID
NOT IN (SELECT CustomerID FROM Invoice WHERE YEAR(OrderDate) = 2009)

This query will return the names of all the customers who have not made an order in 2009. By using the NOT operator we are excluding all of the records from the customer table that have a match in the invoice table.

Should I always Use Subqueries

Although subqueries can be very powerful it is not always the best choice. Sometimes it is more efficient to use a join instead of a subquery to get the desired information. For example when searching for an employee who has not entered an order you could do the following:

SELECT EmpID, LastName, FirstName FROM Employee WHERE EmpID
NOT IN (SELECT EmpID FROM Invoice)

This can be written using a LEFT OUTER JOIN instead. This can be more efficient than using the subquery in many cases.

SELECT e.EmpID, e.LastName, e.FirstName FROM Employee e
LEFT OUTER JOIN Invoice i ON e.EmpId = i.EmpID
WHERE i.OrderID IS NULL

It is important to verify that the new statement returns the same records as the statement using the subquery. An increase in performance is not worth returning an incorrect result.

When rewriting a statement where the subquery has several conditions these conditions must be included in the join rather than in the WHERE clause. The following statement returns the same results as the example in the locating mismatches section.

SELECT i.OrderID, c.LastName, c.FirstName FROM Customer c
LEFT OUTER JOIN Invoice i ON c.CustID = i.CustId AND YEAR(i.OrderDate) = 2008
WHERE i.OrderId is null
ORDER By c.LastName

Notice that the WHERE clause “WHERE YEAR(i.OrderDate) = 2008 and i.OrderId is null” will result in no records being returned, if there is no OrderID then there is no OrderDate.

Subqueries can also be used to insert or update records in one table based on values in another table. However, if you need to update a table with values as well as insert any new records you may want to use the MERGE statement instead. A full discussion of the MERGE statement is available in this tech-tip.

Summary

Subqueries are a powerful feature of SQL. They can be very useful in returning records matching or not matching conditions from other tables. They are very useful when updating tables based on the contents of another table.

However, there are times when using a subquery is not the most efficient way of achieving your goals. Careful consideration is required whenever writing your SQL statements.