SQL Exists

SQL EXISTS Operator

The EXISTS operator tests whether a subquery returns any rows. It returns TRUE if the subquery produces at least one row.

Basic Syntax

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

Example: Customers with Orders

Return customers who have placed at least one order:

SELECT name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
);

The subquery checks whether an order exists for each customer.

NOT EXISTS

Use NOT EXISTS to find rows without a matching record.

SELECT name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
);

This returns customers who have never placed an order.

EXISTS vs IN

• IN compares values to a list of results
• EXISTS checks for the existence of matching rows

Example: Using IN

SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
);

Both queries may return similar results, but performance can differ depending on indexes and dataset size.

Performance Note

EXISTS can be more efficient than IN when the subquery returns many rows, especially when indexed columns are used.

Common Mistakes

• Forgetting to correlate the subquery properly
• Using SELECT * instead of SELECT 1 inside EXISTS (not required)
• Confusing EXISTS logic with JOIN logic

Next Step

Continue with SQL ANY and ALL to compare a value against a subquery result set.