Description

The SQL provides the EXISTS and NOT EXISTS operators to test for the existence of a record in a subquery.

  • The operator EXISTS returns TRUE if the subquery returns one or more records.
  • The operator NOT EXISTS returns FALSE if the subquery returns one or more records.
  • These operators can be used on any SQL statement, like SELECT, INSERT, UPDATE, and DELETE.

Syntax

Here is the basic syntax of the SELECT statement using EXISTS and NOT EXISTS operators.

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

Table Data

Before executing any queries, consider the below data on the tables.

Customers Table Data

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 1 3
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 3 3
10253 2014-07-19 2575.3 4 2
10254 2014-07-24 1256.25 5 3
10255 2014-07-31 9247.5 1 1

SELECT With EXISTS Operator

The below SELECT statement returns the customers having orders of value more than 2000.

Run this on IDE

SELECT *
FROM customers
WHERE EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id AND order_value > 2000);

After successful execution, the output contains the below data.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

SELECT With NOT EXISTS Operator

The below SELECT statement returns the customers not having orders of value more than 2000.

Run this on IDE

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id AND order_value > 2000);

After successful execution, the output contains the below data.

customer_id customer_name address city country postal_code
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Overall

We now know how to test for the existence of records using the EXISTS and NOT EXISTS operators.

Related Links