SQL — Exists

What is EXISTS?

EXISTS returns TRUE if the subquery returns at least one row. It's commonly used in WHERE and NOT EXISTS patterns.

Basic Syntax

SELECT *
FROM table_name
WHERE EXISTS (subquery);

Examples

Find customers who have placed orders

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

Find products that have never been ordered

SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.product_id = p.id
);

EXISTS vs IN

-- Using IN
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NYC');

-- Using EXISTS (often faster for large subqueries)
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE d.id = e.department_id
  AND d.location = 'NYC'
);

Performance Comparison

• IN scans all subquery results and builds a list
• EXISTS stops as soon as it finds the first match (short-circuits)
• For large subqueries, EXISTS is typically faster
• For small subqueries, IN is often fine

NOT EXISTS for Exclusion

-- Find employees who are NOT managers
SELECT e.first_name, e.last_name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees m
  WHERE m.manager_id = e.id
);

EXISTS in SELECT

SELECT
  c.name,
  CASE
    WHEN EXISTS (
      SELECT 1 FROM orders o WHERE o.customer_id = c.id
    ) THEN 'Yes'
    ELSE 'No'
  END AS has_orders
FROM customers c;

EXISTS with Correlated Subqueries

The subquery references the outer query — this is a correlated subquery:

-- Find departments where at least one employee earns above average
SELECT d.department_name
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
  AND e.salary > (SELECT AVG(salary) FROM employees)
);

EXISTS vs COUNT

-- Slower: counts all rows
WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) > 0

-- Faster: stops at first match
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id)

Practical Example

-- Find courses that have at least one enrolled student
SELECT c.course_name
FROM courses c
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.course_id = c.id
  AND e.status = 'active'
);

-- Find courses with zero enrollment
SELECT c.course_name
FROM courses c
WHERE NOT EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.course_id = c.id
);

Best Practices

• Use SELECT 1 in EXISTS — the actual value doesn't matter
• Prefer EXISTS over IN for large subqueries
• Use NOT EXISTS over NOT IN when the subquery may contain NULLs
• Keep the subquery as simple as possible for readability

Practice

1. Find all authors who have written at least one book
2. Find products that have never been reviewed
3. Find employees who manage at least one other employee
4. Rewrite an IN query using EXISTS and compare performance