SQL EXISTS Operator

The EXISTS operator is used to check for the presence of any record in a subquery.

The EXISTS operator returns TRUE if at least the subquery returns at least one row.

The EXISTS operator can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the EXISTS operator is as follows:

SELECT column_list
FROM table_name
WHERE EXISTS 
    ( SELECT column_name FROM table_name WHERE condition);

Note: The SQL statements using the EXISTS operator are inefficient because the subquery is rechecked for every record. Try to rewrite your SQL statement without using the EXISTS operator.

Demo Table

Let us consider the following "Client" and "Order" tables :

• Client table:

  +----+------------+-----------+-------------+---------+
  | id | first_name | last_name | city        | country |
  +----+------------+-----------+-------------+---------+
  | 1  | Maria      | Martinez  | Boston      | USA     |
  | 2  | Donna      | Williams  | Seattle     | USA     |
  | 3  | James      | Smith     | Los Angeles | USA     |
  | 4  | Jennifer   | Garcia    | Las Vegas   | USA     |
  | 5  | Stephanie  | Miller    | Seattle     | USA     |
  | 6  | Robert     | Hernandez | Houston     | USA     |
  | 7  | Andrew     | Rodriguez | Detroit     | USA     |
  +----+------------+-----------+-------------+---------+
  

• Order table:

  +----+-----------+------------+
  | id | client_id | order_date |
  +----+-----------+------------+
  | 1  | 3         | 2018-05-26 |
  | 2  | 5         | 2018-10-01 |
  | 3  | 3         | 2018-10-06 |
  | 4  | 7         | 2018-11-12 |
  | 5  | 6         | 2018-12-16 |
  +----+-----------+------------+
  

SQL EXISTS Examples

The following SQL statement returns the clients who at least passed one order. In another way, it will return the rows from the "Client" table, which has at least one record in the "Order" table.

SELECT *
FROM client
WHERE EXISTS 
    ( SELECT * 
      FROM order 
      WHERE client.id = order.client_id);

After executing the above SQL statement, the output will be as the following:

+----+------------+-----------+-------------+---------+
| id | first_name | last_name | city        | country |
+----+------------+-----------+-------------+---------+
| 3  | James      | Smith     | Los Angeles | USA     |
| 5  | Stephanie  | Miller    | Seattle     | USA     |
| 6  | Robert     | Hernandez | Houston     | USA     |
| 7  | Andrew     | Rodriguez | Detroit     | USA     |
+----+------------+-----------+-------------+---------+

The following SQL statement returns the clients who at least passed one order and are from the "Seattle" city:

SELECT *
FROM client
WHERE EXISTS 
    ( SELECT * 
      FROM order 
      WHERE client.id = order.client_id
      AND city = 'Seattle' );

After the execution, the output will be as follows:

+----+------------+-----------+-------------+---------+
| id | first_name | last_name | city        | country |
+----+------------+-----------+-------------+---------+
| 5  | Stephanie  | Miller    | Seattle     | USA     |
+----+------------+-----------+-------------+---------+