SQL EXISTS Operator

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

Introduction to the SQL EXISTS operator #

The EXISTS operator allows you to check if a subquery returns any row. The EXISTS operator returns true if the subquery returns at least one row or false otherwise.

Here’s the syntax of the EXISTS operator:

SELECT
  column1,
  column2
FROM
  table_name
WHERE
  EXISTS (subquery);Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the EXISTS operator.
Second, place a subquery you want to check for the presence of rows within parentheses.

Typically, you use the EXISTS operator to filter rows in a table based on the existence of rows from a related table. The subquery is usually a correlated subquery that references a column in the outer query.

The EXISTS operator is quite fast because it stops processing when it finds the first matching row.

SQL EXISTS operator example #

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

The following statement uses the EXISTS operator to find 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)

Try it

Output:

 employee_id | first_name  | last_name
-------------+-------------+------------
         100 | Steven      | King
         101 | Neena       | Kochhar
         102 | Lex         | De Haan
         103 | Alexander   | Hunold
...Code language: SQL (Structured Query Language) (sql)

How the query works:

For each row in the employees table:

The subquery checks if the dependents table has a row with the value in employee_id column equals to a value in the employee_id column of the current row from the employees table.
If yes, meaning the current employee has at least one dependent. The subquery returns a row with a value 1. The EXISTS condition evaluates to true. The outer query includes the current row of the employees table in the final result set.
If no, meaning the current employee has no dependents. The subquery returns no row. The EXISTS condition evaluates to false. The outer query does not include the current row from the employees table in the final result set.

To make the query more concise, you can use table aliases for the employees and dependents tables:

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

Try it

SQL NOT EXISTS operator #

To negate the EXISTS operator, you use the NOT operator. The NOT EXISTS returns true if the subquery returns no rows or false otherwise.

Here’s the syntax of the NOT EXISTS operator:

SELECT
  column1,
  column2
FROM
  table_name
WHERE
  NOT EXISTS (subquery);Code language: SQL (Structured Query Language) (sql)

For example, the following query uses the NOT EXISTS operator to find employees who do not have any dependents:

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

Try it

Output:

 employee_id | first_name |  last_name
-------------+------------+-------------
         120 | Matthew    | Weiss
         178 | Kimberely  | Grant
         193 | Britney    | Everett
         177 | Jack       | Livingston
...Code language: SQL (Structured Query Language) (sql)

SQL EXISTS operator and NULL #

If the subquery returns NULL, the EXISTS operator returns true. The reason is that the EXISTS operator only checks for the existence of the row returned by the subquery. It does not matter if the row is NULL or not.

For example, the following query uses the EXISTS operator with a subquery that returns NULL:

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

Try it

The query returns all rows in the employees table.

 first_name  |  last_name
-------------+-------------
 Steven      | King
 Neena       | Kochhar
 Lex         | De Haan
 Alexander   | Hunold
...Code language: SQL (Structured Query Language) (sql)

Summary #

Use the EXISTS operator to test for the existence of rows returned by a subquery.
Use the NOT operator to negate the EXISTS operator.

Quiz #

Databases #

Was this tutorial helpful ?