EXISTS

Question: What does EXISTS do in SQL?

Answer: EXISTS is a filter that checks whether a subquery returns at least one row. It does not care what the subquery selects; it only cares whether a match exists. This makes it great for “does a related row exist?” questions, like customers who have placed an order.

Interview-Ready Answer: I use EXISTS when I want to test for presence, not count. It returns true as soon as the subquery finds one matching row, so it is a natural fit for semi-join logic like “customers who have orders” or “rows that have a related record.” One important detail is that the selected columns inside the subquery usually do not matter; the database only needs to know whether a row exists.

What it is

Detailed Explanation: EXISTS is a boolean predicate, which means it answers a true/false question inside WHERE, HAVING, or another condition. Think of it as a tiny bouncer at the door: it only asks, “Is there at least one matching row in that subquery?” If yes, let the outer row pass; if not, reject it.

How it works under the hood

1. The database takes one row from the outer query.
2. It runs the correlated subquery, meaning the subquery can refer back to columns from the outer row.
3. As soon as the subquery produces the first matching row, EXISTS becomes true and can stop checking more rows.
4. If the subquery produces zero rows, EXISTS is false.
5. The outer row is kept or removed based on that true/false result.

This “stop at the first match” behavior is the key idea. It is why EXISTS often feels fast and natural for presence checks.

When and why to use it

• Use EXISTS when you only need to know whether related rows exist.
• Use NOT EXISTS for anti-matches, such as “customers with no orders.”
• Use it when duplicate rows in the child table should not matter; one match is enough.

Comparison: Many candidates confuse EXISTS with IN or JOIN. They solve related problems, but they are not identical.

Performance notes

In optimizer terms, databases often turn EXISTS into a semi-join, which is a join that only asks whether a match exists instead of returning matching child rows. With a good index on the correlated column, each lookup may be close to an indexed probe, often around O(log n) per outer row in a B-tree index. Without an index, a naive plan can behave more like O(parent × child), which is where slow queries come from.

Realistic numbers: on a table with 1 million customers and 10 million orders, an index on orders.customer_id can turn “does this customer have an order?” from a full scan into a quick lookup. Without that index, the database may inspect far too many rows.

Important edge cases

• The subquery’s selected columns do not affect the result; SELECT 1 is a common style because it makes the intent obvious.
• NULL values can make IN surprising, but EXISTS is usually safer because it checks rows, not membership in a list with three-valued logic.
• NOT EXISTS is the safest way to express “no related rows,” especially when the child column may contain NULL.

Memory Hook: “One match is enough.” Picture a guest list where the bouncer stops reading the list the moment he sees your name once.

Real-World Story: Imagine a checkout service for an online store. Before sending a discount coupon, the system needs to know whether a customer has ever placed an order. The query uses EXISTS so each customer is checked against the orders table, and the database stops at the first matching order instead of counting all orders.

What goes wrong when people misunderstand it? A developer may write a JOIN and forget that a customer with five orders will appear five times. In production, that can cause duplicate coupon emails, duplicate rows in a report, or inflated customer counts. The symptom is often easy to spot: logs show repeated customer IDs, dashboards suddenly jump, and support tickets mention users receiving multiple messages when only one was expected.

Follow-up & Tricky Questions:

• How is EXISTS different from IN? EXISTS checks whether a matching row exists, while IN compares a value against a list of returned values. EXISTS is usually safer when NULL values or large child tables are involved.
• Why do people write SELECT 1 inside EXISTS? Because the column list is ignored by the predicate, so SELECT 1 makes the intent clear and avoids pretending the value matters.
• What does NOT EXISTS mean? It returns true when the subquery produces zero rows. This is the standard way to ask for rows that have no related match.
• What is a correlated subquery? It is a subquery that references a column from the outer query, like o.customer_id = c.customer_id. That link is what lets EXISTS test each outer row independently.
• Can the database optimize EXISTS? Yes. Many engines rewrite it as a semi-join or anti-semi-join, which often allows index-driven lookup and early exit after the first match.
• Tricky: does EXISTS (SELECT NULL ...) behave differently from EXISTS (SELECT 1 ...)? No. The selected expression does not matter; only whether at least one row is returned matters.
• Tricky: does EXISTS care about duplicates in the subquery? No. Once one row is found, the answer is already true, so duplicates do not change the result.
• Tricky: is NOT EXISTS always the same as LEFT JOIN ... IS NULL? Often they are equivalent for the intended logic, but NOT EXISTS is usually clearer and less error-prone when the join condition or null handling gets complicated.

Common Mistakes:

• Using JOIN when you only need presence. Fix: use EXISTS so you do not accidentally duplicate outer rows.
• Thinking the subquery column list matters. Fix: remember the database only checks whether the subquery returns at least one row.
• Ignoring NOT EXISTS for missing-related-row queries. Fix: prefer NOT EXISTS over fragile NOT IN patterns when NULL may appear.
• Forgetting an index on the correlated key. Fix: index the child table column used in the correlation, such as orders.customer_id.

Memory Hook: One match is enough. If the subquery finds a single row, EXISTS is true immediately.

Cheat Sheet:

• EXISTS = “at least one row?”
• NOT EXISTS = “zero related rows?”
• Use with correlated subqueries.
• SELECT 1 is common, but any select list works.
• Great for presence checks and anti-joins.
• Index the correlated column for speed.

Practice Tasks:

• Write a query for “products that have been ordered at least once.”
• Write a query for “employees who have no assigned projects.”
• Compare EXISTS, IN, and JOIN on a small dataset with duplicates and a NULL value.