Using WHERE EXISTS in SQL

Kris Wenzel

·

·

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:

  1. The WHERE clause returns all records where the EXISTS clause is TRUE.
  2. The EXIST clause uses a correlated subquery. The outer query is correlated to the inner query by SalesPersonID.
  3. Only SalesPersons with SalesYTD greater than three million are included in the results.
  4. 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:

10 responses to “Using WHERE EXISTS in SQL”
  1. Use Subquery with Bill of Materials – Essential SQL

    […] The subquery is correlated with the main query via ProductID.  Meaning, for each Product, a query is run to find BOM entries whose ComponentID or ProductAssemblyID matches the Product.ProductID.  You can learn more about these subqueries in my article Subqueries in the Where Clause. […]

    Reply
  2. Renátó Németh

    Hello Kris

    Can you help me please?
    I have a subquery in the where clause which gives back the name of a column, but the SQL doesn’t recognize the result. So when I run the query it returns an empty table.

    The code looks like this:

    SELECT * FROM projects where (SELECT CONCAT(“`p_”,id,”`”) from properties where id in (select SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ‘,’, numbers.n), ‘,’, -1) from (select 1 n union all select 2 union all select 3 union all select 4 union all select 5) numbers INNER JOIN filters_properties on CHAR_LENGTH(ids) -CHAR_LENGTH(REPLACE(ids, ‘,’, ”))>=numbers.n-1 WHERE filter_id = (SELECT id FROM filters WHERE name = ‘GYMS’))) = 1;

    Another problem: when the subquery returns more than 1 row, I don’t know what to do with it. How can I separate them to get a result like (p_1 = 1 or p_2 = 1 or p_3 = 1)?

    Reply
  3. Ian Johns

    Hi Kris, suggestion…

    This format of compound elements in the where clause and sub-queries has been useful to me over the years. It’s rare to see it in documentation. If you decide to revise the blog post, think about including examples like this.

    SELECT
    table1.*
    FROM
    table1
    WHERE
    (field1, field2) in ( (1, 125788 ), (1, 127753), (1, 301852) )
    ;

    Of course, instead of specific values on the right side of the “in”, you can have a subquery that returns the same number of values.

    Reply
    1. Kris Wenzel

      That is a great suggestion. I’ll follow you up on that!

      Kris.

      Reply
    2. Harold Sikkema

      This was helpful to me. Thanks.

      Reply
  4. SAF

    Hi Kris, I need to check a date column in a subquery for any nulls and return a boolean to use in the outer query. How would you do this? where would you place the subquery in the where, from, select? Any help would be greatly appreciated.

    Reply
    1. Kris Wenzel

      Is the data column part of a larger query, or are you just trying to check for the data column in the main query’s current row?

      Can you show an example output?

      Reply
  5. Phil G

    Thank you, this was very helpful!

    Reply
    1. Kris Wenzel

      I’m glad you find the site helpful. Come back soon!

      Reply
  6. Hannes

    Thanks!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table Of Contents

Add a header to begin generating the table of contents

More from the blog

MySQL PostgreSQL SQLite SqlServer