SQL EXISTS Practice Exercises

What is EXISTS?

EXISTS tests whether a subquery returns any rows at all. It doesn't care what those rows contain — only whether at least one exists. The result is a simple true or false for each row the outer query checks.

-- Employees who have at least one colleague in the same
-- department earning more than they do
SELECT e1.employee_id, e1.department, e1.salary
FROM employee_records e1
WHERE EXISTS (
    SELECT 1
    FROM employee_records e2
    WHERE e2.department = e1.department
      AND e2.salary > e1.salary
);

Because EXISTS only checks for the presence of rows, the inner SELECT list doesn't matter — SELECT 1 is a common convention that signals "I just need to know if anything matches."

EXISTS is almost always correlated

EXISTS is most useful when the subquery references the outer row, so the existence check changes for every row being evaluated. Just like a correlated subquery, this requires an alias on both copies of the table.

-- Sales with the highest price for their own drink type
SELECT s1.sale_id, s1.drink_type, s1.price_usd
FROM coffee_shop_sales s1
WHERE NOT EXISTS (
    SELECT 1
    FROM coffee_shop_sales s2
    WHERE s2.drink_type = s1.drink_type
      AND s2.price_usd > s1.price_usd
);

NOT EXISTS flips the logic: a row is kept only when the subquery finds no match. Here, it keeps only the sales where nothing of the same drink type costs more — in other words, the top price per drink type.

A safer alternative to NOT IN

Because NOT IN breaks unexpectedly when its subquery contains even one NULL, many SQL developers prefer NOT EXISTS for "doesn't match anything" logic — it doesn't have that pitfall, since it's just checking for the absence of matching rows.

-- Products with no matching row in the same category priced higher
SELECT p1.product_id, p1.category, p1.unit_cost
FROM product_inventory p1
WHERE NOT EXISTS (
    SELECT 1
    FROM product_inventory p2
    WHERE p2.category = p1.category
      AND p2.unit_cost > p1.unit_cost
);

Common mistakes

Forgetting to correlate — if the subquery inside EXISTS doesn't reference the outer row, it returns the same true/false result for every row, which is rarely what you want.

Selecting specific columns unnecessarily — since EXISTS only checks for row presence, selecting real columns inside it adds no value and can be confusing. SELECT 1 or SELECT * both work identically.

Reaching for NOT IN instead of NOT EXISTS — when checking for "no match," NOT IN can silently return zero rows if the subquery includes a NULL. NOT EXISTS avoids this trap entirely.

Missing aliases — both the outer and inner references to the same table need their own alias, or SQL won't be able to tell which one a column belongs to.