SQL EXISTS

Summary: in this tutorial, you will learn how to use the SQL EXISTS operator to test if a subquery contains any rows.

Introduction to the SQL EXISTS operator

The EXISTS operator allows you to specify a subquery to test for the existence of rows. The following illustrates the syntax of the EXISTS operator:

EXISTS (subquery)
Code language: SQL (Structured Query Language) (sql)

The EXISTS operator returns true if the subquery contains any rows. Otherwise, it returns false.

The EXISTS operator terminates the query processing immediately once it finds a row, therefore, you can leverage this feature of the EXISTS operator to improve the query performance.

SQL EXISTS operator example

We will use the  employees and dependents tables in the sample database for the demonstration.

The following statement finds all employees who have at least one dependent:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    EXISTS( SELECT 
            1
        FROM
            dependents
        WHERE
            dependents.employee_id = employees.employee_id);
Code language: SQL (Structured Query Language) (sql)

The subquery is correlated. For each row in the  employees table, the subquery checks if there is a corresponding row in the dependents table. If yes, then the subquery returns one which makes the outer query to include the current row in the  employees table. If there is no corresponding row, then the subquery returns no row that causes the outer query to not include the current row in the  employees table in the result set.

SQL NOT EXISTS

To negate the EXISTS operator, you use the NOT operator as follows:

NOT EXISTS (subquery)
Code language: SQL (Structured Query Language) (sql)

For example, the following query finds employees who do not have any dependents:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            dependents
        WHERE
            dependents.employee_id = employees.employee_id);
Code language: SQL (Structured Query Language) (sql)

The following screenshot illustrates the result:

SQL EXISTS and NULL

If the subquery returns NULL, the EXISTS operator still returns the result set. This is because the EXISTS operator only checks for the existence of row returned by the subquery. It does not matter if the row is NULL or not.

In the following example, the subquery returns NULL but the EXISTS operator still evaluates to true:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    EXISTS( SELECT NULL)
ORDER BY first_name , last_name;    
Code language: SQL (Structured Query Language) (sql)

The query returns all rows in the  employees table.

In this tutorial, you have learned how to use the SQL EXISTS operator to test for the existence of rows returned by a subquery.