SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

Syntax

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

Example: EXISTS with a Subquery

SELECT supplier_name
FROM suppliers
WHERE EXISTS (SELECT product_name FROM products WHERE products.supplier_id = suppliers.supplier_id AND price > 20);

Sample Tables

Consider the following suppliers and products tables:

suppliers:
| supplier_id | supplier_name |
|-------------|---------------|
| 1           | Supplier A    |
| 2           | Supplier B    |
| 3           | Supplier C    |

products:
| product_id | product_name | supplier_id | price |
|------------|--------------|-------------|-------|
| 1          | Product 1    | 1           | 18    |
| 2          | Product 2    | 1           | 22    |
| 3          | Product 3    | 2           | 19    |
| 4          | Product 4    | 3           | 25    |

Result of EXISTS

| supplier_name |
|---------------|
| Supplier A    |
| Supplier C    |

The result shows suppliers with at least one product priced above 20.

Example: EXISTS with NOT

SELECT customer_name
FROM customers
WHERE NOT EXISTS (SELECT order_id FROM orders WHERE customers.customer_id = orders.customer_id);

Sample Tables

Consider the following customers and orders tables:

customers:
| customer_id | customer_name |
|-------------|---------------|
| 1           | Customer A    |
| 2           | Customer B    |
| 3           | Customer C    |

orders:
| order_id | customer_id | order_date |
|----------|-------------|------------|
| 1        | 1           | 2023-01-01 |
| 2        | 1           | 2023-01-15 |
| 3        | 2           | 2023-02-10 |

Result of NOT EXISTS

| customer_name |
|---------------|
| Customer C    |

The result shows customers who have not placed any orders.