Tip

Combining T-SQL subqueries with operators in SQL Server

Part one of this series looked at the fundamentals of T-SQL subqueries with details on how to use them as expressions. Part two breaks down how to use

    Requires Free Membership to View

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.

This was first published in July 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.