Many real questions aren't "combine these tables" but "does a matching row exist?" (a semi-join) or "is there no matching row?" (an anti-join): customers who ordered, products never sold, users who never logged in. This guide covers EXISTS, IN, ANY and ALL, the three anti-join patterns, and how to choose.
EXISTS and NOT EXISTS
-- Customers who have placed at least one order
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Same using JOIN (usually faster):
SELECT DISTINCT c.name FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
-- Customers with NO orders (NOT EXISTS)
SELECT name FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Why SELECT 1? Because EXISTS only checks IF a row exists, not what it contains.
-- SELECT 1, SELECT *, SELECT 'x' — all work the same with EXISTS.| Method | Use When | Performance |
|---|---|---|
| IN (subquery) | Small result set from subquery | Fast for small lists; slow for large |
| EXISTS | Checking row existence, large subquery | Stops at first match — efficient |
| NOT IN | Small lists with NO NULLs | Dangerous with NULLs — use NOT EXISTS |
| NOT EXISTS | Safe alternative to NOT IN | Best for large tables with NULLs |
| JOIN | When you need data from both tables | Usually fastest overall |
ANY and ALL
-- ANY: true if condition is true for AT LEAST ONE value in subquery
SELECT name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'HR');
-- Returns employees earning more than at least one HR employee
-- ALL: true if condition is true for EVERY value in subquery
SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept = 'HR');
-- Returns employees earning more than the HIGHEST HR salary
-- Equivalents:
-- > ANY ≡ > MIN(subquery)
-- > ALL ≡ > MAX(subquery)
-- = ANY ≡ IN (subquery)Semi-Joins and Anti-Joins
SQL does not have SEMI JOIN or ANTI JOIN keywords.
These are LOGICAL JOIN TYPES that you implement using EXISTS, IN, NOT EXISTS, NOT IN.
Understanding the terminology helps you communicate clearly in interviews.
Semi-Join — "Does a matching row exist?"
A semi-join returns rows from the LEFT table where a match EXISTS in the right table.
Unlike INNER JOIN, it does NOT duplicate left rows when multiple right rows match.
It only returns columns from the left table.
Implemented via: WHERE EXISTS (...) or WHERE col IN (subquery)
-- Semi-join: customers who have placed at least one order
-- Method 1: EXISTS (semi-join — stops at first match, efficient)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Method 2: IN
SELECT customer_id, name FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);
-- Method 3: INNER JOIN (NOT a true semi-join — duplicates left rows!)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
-- DISTINCT needed to de-duplicate — less efficient than EXISTS
-- ✅ BEST PRACTICE: Use EXISTS for semi-joins
-- EXISTS stops scanning as soon as ONE match is found per outer row.
-- IN requires building the full subquery result set first.
-- For large tables, EXISTS is significantly faster.Anti-Join — "No matching row exists"
An anti-join returns rows from the LEFT table where NO match exists in the right table.
Implemented via: WHERE NOT EXISTS (...) or LEFT JOIN + IS NULL
AVOID: WHERE col NOT IN (subquery) — dangerous with NULLs!
-- Anti-join: customers who have NEVER placed an order
-- Method 1: NOT EXISTS ✅ (safe, efficient, handles NULLs)
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Method 2: LEFT JOIN + IS NULL ✅ (also safe and common)
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
-- Method 3: NOT IN ❌ DANGEROUS
SELECT customer_id, name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- BUG: If ANY order has customer_id = NULL, this returns ZERO rows!
-- Fix NOT IN to be safe (explicitly exclude NULLs):
WHERE customer_id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);Complete Performance Comparison:
| Method | Semi/Anti? | NULL Safe? | Performance (large tables) | Recommended? |
|---|---|---|---|---|
| EXISTS | Semi-join | Yes | Fast — stops at first match | ✅ Yes |
| IN (subquery) | Semi-join | Yes (for semi) | Medium — builds full subquery set | ✅ OK for small sets |
| INNER JOIN + DISTINCT | Semi-join | Yes | Slower — dedup overhead | ⚠️ Use EXISTS instead |
| NOT EXISTS | Anti-join | Yes | Fast — stops at first match | ✅ Best for anti-join |
| LEFT JOIN + IS NULL | Anti-join | Yes | Medium — full join then filter | ✅ Good alternative |
| NOT IN | Anti-join | NO — NULLs break it! | Slow AND dangerous | ❌ Avoid unless NULL-safe |
The Full JOIN Decision Tree
Q: Do you need columns from BOTH tables?
YES → Use INNER JOIN (matched rows only) or LEFT/RIGHT/FULL JOIN (include unmatched)
NO, just checking existence:
Q: Does the row EXIST in the other table?
YES → Use EXISTS / IN (semi-join)
NO → Use NOT EXISTS / LEFT JOIN + IS NULL (anti-join)
Q: Can the right table have NULLs in the join column?
YES → Never use NOT IN. Use NOT EXISTS or LEFT JOIN + IS NULL.
Q: Will one left row match MULTIPLE right rows?
YES + you want each combination → INNER JOIN (may produce duplicate left rows)
YES + you want left rows once → EXISTS or DISTINCT
FAQs
What is an anti-join in SQL?
A query that returns rows from one table with no match in another, written with NOT EXISTS, LEFT JOIN … WHERE right.id IS NULL, or NOT IN (only if NULLs are impossible).
Is EXISTS faster than IN?
Modern optimizers often produce the same plan for both. EXISTS stops at the first match and is safe with NULLs, so it's a reliable default for correlated checks.
Why prefer EXISTS over JOIN for "has any" questions?
A JOIN returns one row per match, so customers with five orders appear five times and need DISTINCT; EXISTS returns each customer once.
