Lessons › SQL › Deep Dive: Advanced Joins & Subqueries

SQL lesson

EXISTS and NOT EXISTS

EXISTS checks whether at least one matching row turns up somewhere else in your data - it's a yes-or-no test, not a count. NOT EXISTS flips that answer, keeping only the rows where nothing matched.

Example

Checking Related Retail Records with EXISTS and NOT EXISTS

SELECT c.customer_id, c.region
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
)

Result:

Step by step

1. After SELECT, list the column names you want, separated by commas.

   SELECT c.customer_id, c.region

2. FROM tells SQL which table to pull the data from.

   SELECT c.customer_id, c.region
   FROM customers c

3. WHERE keeps only the rows that match your condition.

   SELECT c.customer_id, c.region
   FROM customers c
   WHERE EXISTS (
     SELECT 1
     FROM orders o
     WHERE o.customer_id = c.customer_id
   );

Practice

Try each one in your head first, then open the answer.

SELECT * FROM orders WHERE NOT EXISTS (SELECT 1 FROM order_details WHERE order_details.order_id = orders.order_id)