Combining T-SQL subqueries with operators in SQL Server

Part two breaks down how to use subqueries with comparison operators, IN and NOT IN operator,s and EXIST and NOT EXIST operators, with examples of each.

Using subqueries with comparison operators

Using subqueries in the search condition of a WHERE clause is a common scenario, particularly when used with a comparison operator. Let’s look at a few examples of SELECT statements that use comparison operators with subqueries. In the following SELECT statement, I use a subquery to find the ProductSubcategoryID value for the subcategory Brakes:

SELECT
  ProductID,
  Name,
  ProductSubcategoryID
FROM Production.Product
WHERE ProductSubcategoryID =
  (
    SELECT ProductSubcategoryID
    FROM Production.ProductSubcategory
    WHERE Name = 'brakes'
  )

In this case, the subquery returns a ProductSubcategoryID value of 6. That value is then compared to the ProductSubcategoryID value in the Product table. If the values match, the row is returned. Notice that I used the equals (=) comparison operator to compare the ProductSubcategoryID value to the subquery results. The following table shows the result set returned by the outer SELECT statement:

ProductID	Name	ProductSubcategoryID
907	Rear Brakes	6
948	Front Brakes	6

You can also use other comparison operators when comparing a column value to a subquery result set. For example, the following statement uses the not equal (<>) comparison operator:

SELECT
  ProductID,
  Name,
  ProductSubcategoryID
FROM Production.Product
WHERE ProductSubcategoryID <>
  (
    SELECT ProductSubcategoryID
    FROM Production.ProductSubcategory
    WHERE Name = 'mountain bikes'
  )

In this case, the subquery returns the ProductSubcategoryID for the Mountain Bikes subcategory, which is 1. As a result, the outer SELECT statement will return all rows except those that have a ProductSubcategoryID of 1.

In the following example, I use the greater than (>) comparison operator to compare the SalesYTD value to the subquery:

SELECT
  FirstName + ' ' + LastName AS FullName,
  ROUND(SalesYTD, 2) AS SalesYTD
FROM
  Sales.vSalesPerson
WHERE
  SalesYTD >
   (
     SELECT AVG(SalesYTD)
     FROM Sales.vSalesPerson
     WHERE JobTitle = 'Sales Representative'
   )

The subquery returns the average amount of sales for all sales representatives, which is 3,054,352.7524. As a result, only reps whose sales exceed that amount are included in the outer statement’s result set, as shown in the following table:

FullName	SalesYTD
Michael Blythe	4557045.05
Linda Mitchell	5200475.23
Jillian Carson	3857163.63
José Saraiva	3189356.25
David Campbell	3587378.43
Jae Pak	5015682.38
Ranjit Varkey Chudukatil	3827950.24

When you use a comparison operator to compare a value to a subquery, that subquery must return a single value unless you also include the ALL or ANY operator. For the ALL operator, the compared value must exceed all the values returned by the subquery. For the ANY operator, the compared value must exceed any of the values returned by the subquery.

In the following example, I used the ALL operator when comparing the SalesYTD column to the subquery:

SELECT
  FirstName + ' ' + LastName AS FullName,
  JobTitle,
  ROUND(SalesYTD, 2) AS SalesYTD
FROM
  Sales.vSalesPerson
WHERE
  SalesYTD > ALL
   (
     SELECT AVG(SalesYTD)
     FROM Sales.vSalesPerson
     GROUP BY JobTitle
   )

Because I used the ALL operator, the subquery can return more than one value. In this case, the subquery returns the average amount of sales for each job group. The following table shows the results returned by the subquery:

636440.251

677558.4653

219088.8836

3054352.7524

The SalesYTD value must be greater than all the values returned by the subquery. That means the SalesYTD value must exceed 3,054,352.7524. The following table shows the results returned by the outer SELECT statement:

FullName	JobTitle	SalesYTD
Michael Blythe	Sales Representative	4557045.05
Linda Mitchell	Sales Representative	5200475.23
Jillian Carson	Sales Representative	3857163.63
José Saraiva	Sales Representative	3189356.25
David Campbell	Sales Representative	3587378.43
Jae Pak	Sales Representative	5015682.38
Ranjit Varkey Chudukatil	Sales Representative	3827950.24

As you can see, only the sales representatives whose sales exceed 3,054,352.7524 are included in the results.

Using subqueries with the IN and NOT IN operators

In addition to using a comparison operator in a WHERE clause that includes a subquery, you can use the IN or NOT IN operator. These operators let you compare a value to zero or more values returned by the subquery. For the IN operator, if the value is not in the subquery result set, the search condition returns false and the row is not included. Otherwise, a true is returned and the row is included. (The NOT IN operator is just the opposite.)

In the following example, I use an IN operator to compare the TerritoryID value to the subquery result set:

SELECT
  BusinessEntityID,
  TerritoryID,
  SalesQuota,
  SalesYTD
FROM
  Sales.SalesPerson
WHERE
  TerritoryID IN
  (
    SELECT TerritoryID
    FROM Sales.SalesTerritory
    WHERE Name = 'northeast'
     OR Name = 'northwest'
  )
ORDER BY TerritoryID, BusinessEntityID

The subquery returns the TerritoryID values for the Northeast and Northwest regions, which are 2 and 1 respectively. As a result, the TerritoryID value in the SalesPerson table must equal 1 or 2 for the row to be included in the result set of the outer table, as shown in the following table:

BusinessEntityID	TerritoryID	SalesQuota	SalesYTD
280	1	250000.00	0.00
283	1	250000.00	3587378.4257
284	1	300000.00	1931620.1835
275	2	300000.00	4557045.0459

The next example is identical to the previous one, except that I used the NOT IN operator:

SELECT
  BusinessEntityID,
  TerritoryID,
  SalesQuota,
  SalesYTD
FROM
  Sales.SalesPerson
WHERE
  TerritoryID NOT IN
  (
    SELECT TerritoryID
    FROM Sales.SalesTerritory
    WHERE Name = 'northeast'
    OR Name = 'northwest'
  )
ORDER BY TerritoryID, BusinessEntityID

Now the outer statement returns all rows except those whose TerritoryID value equals 1 or 2, as shown in the following table:

BusinessEntityID	TerritoryID	SalesQuota	SalesYTD
277	3	250000.00	3857163.6332
276	4	250000.00	5200475.2313
281	4	250000.00	3018725.4858
279	5	300000.00	2811012.7151
278	6	250000.00	1764938.9859
282	6	250000.00	3189356.2465
290	7	250000.00	3827950.238
288	8	250000.00	2241204.0424
286	9	250000.00	1758385.926
289	10	250000.00	5015682.3752

You can also use the IN or NOT IN operators in statements other than the SELECT statement. For instance, the following UPDATE statement compares the ModifiedDate value to the subquery results:

UPDATEProduction.Illustration
SET Diagram = NULL
WHERE ModifiedDate IN
 (
   SELECT ModifiedDate
   FROM Production.Illustration
   WHERE ModifiedDate > '2005-01-01 00:00:00.000'
 )

The subquery returns all rows whose modified dates are later than January 1, 2005. If the ModifiedDate value in the row returned by the Illustration table is later than that date, the row is updated and the Diagram column is set to NULL. Otherwise, no changes are made.

Using subqueries with the EXIST and NOT EXIST operators

Another set of operators you can use in a WHERE clause that contains a subquery are the EXIST and NOT EXIST operators. The EXIST operator checks whether the subquery returns any value. If one or more values are returned, the row is returned, otherwise it is not. (Once again, the NOT EXIST operator is just the opposite of this.)

In the following SELECT statement I checked for the existence of a product with a name that includes “brake:”

SELECT
  ProductID,
  Name,
  ProductModelID
FROM Production.Product
WHERE EXISTS
  (
    SELECT *
    FROM Production.ProductModel
    WHERE ProductModelID =  Production.Product.
      ProductModelID
      AND Name LIKE '%brakes%'
  )

First, notice that the subquery SELECT statement uses an asterisk (*) for the SELECT list. Since you’re only checking for the existence of a returned row, column names aren’t needed. Also notice that I created a correlated subquery so the ProductModelID values can be matched between the outer query and the subquery. As a result, a row has to have matching ProductModelID values and the name must include “brakes.” If the subquery returns a value, the EXIST operator returns a true, and the row is returned. The following table shows the results returned by the outer statement:

ProductID	Name	ProductModelID
907	Rear Brakes	128
948	Front Brakes	102

You can just as easily use the NOT EXIST operator to check whether any results are returned. The following example is identical to the previous one, except that the NOT EXIST operator is used:

SELECT
  ProductID,
  Name,
  ProductModelID
FROM Production.Product
WHERE NOT EXISTS
  (
    SELECT *
    FROM Production.ProductModel
    WHERE ProductModelID = Production.Product.
      ProductModelID
      AND Name LIKE '%brakes%'
  )

Now the statement returns 502 rows rather than 2; one row each time the subquery does not return a result set.

As you’ve seen in the above examples, a subquery provides a lot of flexibility when you need to work with subsets of data. For more details about subqueries and to see additional examples, refer to SQL Server Books Online. In addition, be sure to keep in mind that in some cases a join might be a better solution than a subquery. In the meantime, try out various types of subqueries and vary their complexity to gain a better understanding of the power they can add to your statements.

Back to part one: Applying subqueries to T-SQL statements

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at www.rhsheldon.com.