Share via

  • Article

Subqueries with EXISTS

When a subquery is introduced with the keyword EXISTS, the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

A subquery introduced with EXISTS has the following syntax:

WHERE [NOT] EXISTS (subquery)

The following query finds the names of all products that are in the Wheels subcategory:

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE EXISTS
    (SELECT * 
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID = 
            Production.Product.ProductSubcategoryID
        AND Name = 'Wheels')

Here is the result set.

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

To understand the results of this query, consider the name of each product in turn. Does this value cause the subquery to return at least one row? In other words, does the query cause the existence test to evaluate to TRUE?

Notice that subqueries that are introduced with EXISTS are a bit different from other subqueries in the following ways:

  • The keyword EXISTS is not preceded by a column name, constant, or other expression.

  • The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

The EXISTS keyword is important because frequently there is no alternative, nonsubquery formulation. Although some queries that are created with EXISTS cannot be expressed any other way, many queries can use IN or a comparison operator modified by ANY or ALL to achieve similar results.

For example, the preceding query can be expressed by using IN:

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')