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
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
USE AdventureWorks; GO SELECT Name FROM Production.Product WHERE ProductSubcategoryID IN (SELECT ProductSubcategoryID FROM Production.ProductSubcategory WHERE Name = 'Wheels')