The SQL EXISTS operator is mostly used to test whether a subquery returns rows. It returns TRUE if rows exists in the subquery and FALSE if they do not.
SQL Exists
The EXISTS condition is used in combination with a subquery. It returns TRUE whenever the subquery returns one or more values.
In its simplest form the syntax for the SQL EXISTS condition is
WHERE EXISTS (sub query)
Suppose we need to return all sales orders written by salespeople with sales year to date greater than three million dollars. To do so we can use the EXISTS clause as shown in this example:
SELECT SalesOrderID,
RevisionNumber,
OrderDate
FROM Sales.SalesOrderHeader
WHERE EXISTS (SELECT 1
FROM sales.SalesPerson
WHERE SalesYTD > 3000000
AND SalesOrderHeader.SalesPersonID
= Sales.SalesPerson.BusinessEntityID)
When this SQL executes the following comparisons are made:
- The WHERE clause returns all records where the EXISTS clause is TRUE.
- The EXIST clause uses a correlated subquery. The outer query is correlated to the inner query by SalesPersonID.
- Only SalesPersons with SalesYTD greater than three million are included in the results.
- The EXISTS clause returns TRUE if one or more rows are returned by the subquery.
The EXISTS condition is a membership condition in the sense it only returns TRUE if a result is returned.
SQL NOT EXISTS
Conversely, if we want to test for non-membership we can use NOT EXISTS.
NOT EXISTS returns TRUE if zero rows are returned. So, if we want to find all sales orders that were written by salespeople that didn’t have 3,000,000 in year-to-date sales, we can use the following query:
SELECT SalesOrderID,
RevisionNumber,
OrderDate
FROM Sales.SalesOrderHeader
WHERE NOT EXISTS (SELECT 1
FROM sales.SalesPerson
WHERE SalesYTD > 3000000
AND SalesOrderHeader.SalesPersonID
= Sales.SalesPerson.BusinessEntityID)
WHAT happens to NULL?
When the subquery returns a NULL what does EXIST return: NULL, TRUE, or FALSE?
To be honest I was surprised.
I was sure it would return NULL, but to my surprise, I learned it returns TRUE. Therefore, if your subquery returns a NULL value, the SQL EXISTS statement resolves to TRUE. In the following example all the SalesOrderHeader rows are returned as the WHERE clause essentially resolved to TRUE:
SELECT SalesOrderID, RevisionNumber, OrderDate FROM Sales.SalesOrderHeader WHERE EXISTS (SELECT NULL)
As we study the IN operator, we’ll see this behavior is unique to the EXISTS clause.
Learn More
What to read next:
Leave a Reply