The Most Common SQL JOIN Mistakes (And How to Avoid Them)

SQL JOINs are tested in almost every data role interview and SQL certification exam. Most people understand the concept — combine rows from two tables — but the details are where marks get lost.

Here are the mistakes that come up most often, and exactly how to fix them.

1. Confusing INNER JOIN with LEFT JOIN

This is the most common source of wrong answers in SQL exams. The difference:

-- INNER JOIN: only rows that match in BOTH tables
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN: ALL rows from orders, matched or not
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

With a LEFT JOIN, if there's no matching customer, c.name will be NULL — not missing from results entirely.

Exam tip: If a question says "include all orders, even those without a customer" — that's a LEFT JOIN. "Only orders with a matching customer" — INNER JOIN.

2. Forgetting that JOINs can multiply rows

If your JOIN condition isn't specific enough, you can get more rows back than you expect — a Cartesian product.

-- orders has 100 rows, products has 50 rows
-- This gives 5,000 rows (100 × 50):
SELECT * FROM orders, products;

-- Always specify the join condition:
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id;

What to know: A missing ON clause or a wrong condition creates a Cartesian product. Always check your row count makes sense.

3. Using WHERE instead of HAVING after GROUP BY

This is tested constantly. WHERE filters rows before grouping; HAVING filters groups after.

-- WRONG: can't use aggregate in WHERE
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE COUNT(*) > 5        -- Error!
GROUP BY customer_id;

-- CORRECT: use HAVING for aggregate conditions
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Rule: If you're filtering on an aggregate (COUNT, SUM, AVG), use HAVING. Everything else uses WHERE.

4. Not handling NULLs correctly in JOIN conditions

NULL does not equal NULL in SQL. This catches people out when joining on nullable columns.

-- This will NOT match rows where ref_code is NULL on both sides:
SELECT * FROM a JOIN b ON a.ref_code = b.ref_code;

-- To include NULLs:
SELECT * FROM a JOIN b
ON a.ref_code = b.ref_code
   OR (a.ref_code IS NULL AND b.ref_code IS NULL);

What to know: NULL = NULL evaluates to UNKNOWN, not TRUE. Always use IS NULL or IS NOT NULL.

5. Not knowing when to use a subquery vs a JOIN

Both can solve the same problem, but exams test whether you know the difference.

-- Using a subquery:
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- Using a JOIN (often more efficient):
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;

What to know: JOINs are generally faster on large datasets. Subqueries are clearer when you need to express "find rows where something exists in another table". EXISTS is often better than IN for subqueries.

Find your exact weak spots

Reading these examples is a start. But the only way to know if you've actually internalised them is to answer questions under test conditions — and see which ones you get wrong.

The Examifyr SQL practice test gives you 25 real exam-style questions, an instant score, and a topic breakdown showing exactly where your gaps are.

Think you're ready? Prove it.

Take the free SQL readiness test. Get a score from 0–100, a topic breakdown, and your exact weak areas — in under 20 minutes.

Free · No sign-up · Instant results

More from Examifyr