0Pricing
SQL Interview Prep · Lesson

Choosing the Right Join Type

A decision framework for picking the correct join from a word problem.

Choosing the Right Join Type is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Picking the Join From a Word Problem

The highest-value join skill in an interview is not syntax, it is translating a plain-English requirement into the correct join type. Most candidates know how each join works but freeze when they must choose one under pressure.

This lesson gives you a repeatable decision framework so you can name the right join in seconds.

The Core Question: Which Rows Must Survive?

Every join choice reduces to one question: which side's unmatched rows do you need to keep?

  • Keep only matched rows on both sides: INNER JOIN.
  • Keep all left rows, matched or not: LEFT JOIN.
  • Keep all rows from both sides: FULL OUTER JOIN.
  • Keep every combination: CROSS JOIN.

Decide what must survive first; the join type follows automatically.

Signal Words for INNER JOIN

Phrases like 'customers who placed an order,' 'products that have reviews,' or 'orders along with their customer' all imply a match must exist on both sides. That is an INNER JOIN.

If dropping unmatched rows is acceptable or desired, inner join is the default and the most efficient choice.

SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Signal Words for LEFT JOIN

Phrases like 'all customers, including those with no orders,' or 'list every product and its review count (zero if none)' mean you must preserve the left table regardless of matches. That is a LEFT JOIN.

The giveaway words are 'all,' 'including those without,' and 'even if none.'

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Signal Words for the Anti-Join

Phrases like 'customers who have never ordered,' 'products with no sales,' or 'employees with no manager' ask for rows that have no match. This is the anti-join: LEFT JOIN plus WHERE right_key IS NULL (or NOT EXISTS).

The keyword is 'never,' 'no,' or 'without.'

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;

Signal Words for FULL OUTER JOIN

Phrases like 'reconcile two lists and show entries missing from either side' or 'compare last month's accounts to this month's, flagging both new and dropped' require keeping unmatched rows from both tables. That is a FULL OUTER JOIN.

Reconciliation and diff problems are the typical trigger.

SELECT a.id, b.id
FROM last_month a
FULL OUTER JOIN this_month b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;

Signal Words for CROSS JOIN

Phrases like 'every size in every color,' 'one row per store per day even when there were no sales,' or 'all possible combinations' point to a CROSS JOIN.

The tell is 'every... in every' or 'all combinations,' where no matching condition exists between the two sets.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Signal Words for SELF JOIN

Phrases that relate a row to another row in the same table, such as 'each employee and their manager,' 'pairs of users in the same city,' or 'find duplicate emails,' indicate a self join.

The clue is that one entity references or is compared to another of the same kind.

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

A Worked Translation

Prompt: 'Show every product and how many times it was ordered last month, including products that were never ordered.'

Decode it: 'every product' plus 'including never ordered' means preserve all product rows, so LEFT JOIN products to orders, then COUNT the order side (which is 0 for unmatched products because COUNT ignores NULL).

SELECT p.name, COUNT(o.order_id) AS times_ordered
FROM products p
LEFT JOIN orders o
  ON o.product_id = p.id
 AND o.order_date >= DATE '2024-05-01'
GROUP BY p.id, p.name;

A Common Trap: Filter in ON vs WHERE

In the previous query the date filter sits in the ON clause, not WHERE. That keeps products with zero qualifying orders.

If you moved order_date >= ... into WHERE, it would discard the NULL rows from unmatched products and silently turn the LEFT JOIN into an INNER JOIN, dropping the very products the prompt asked to keep.

-- WRONG: WHERE on the outer table removes unmatched products
-- ...
-- LEFT JOIN orders o ON o.product_id = p.id
-- WHERE o.order_date >= '2024-05-01'  <-- becomes an inner join

The Decision Framework

Run any join word problem through this checklist:

  • Same table related to itself? -> SELF JOIN (and consider a recursive CTE if depth is unbounded).
  • Every combination, no condition? -> CROSS JOIN.
  • Only matched rows? -> INNER JOIN.
  • Keep all of one side? -> LEFT JOIN.
  • Keep unmatched from both? -> FULL OUTER JOIN.
  • Rows with no match? -> anti-join (LEFT JOIN + IS NULL).

Quick Check

Apply the framework to a word problem.

Recap: Choosing the Right Join Type

Key takeaways:

  • Start every join decision by asking which unmatched rows must survive.
  • Map signal words: 'all/including' -> LEFT, 'never/no' -> anti-join, 'every...in every' -> CROSS, 'each X and its X' -> SELF, reconciliation -> FULL OUTER.
  • Keep outer-join filters in the ON clause; a filter in WHERE can silently demote a LEFT JOIN to an INNER JOIN.
  • Verbalize the framework in interviews, it shows reasoning, not memorization.

Frequently asked questions

Is the “Choosing the Right Join Type” lesson free?

Yes — the full text of “Choosing the Right Join Type” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Choosing the Right Join Type”?

A decision framework for picking the correct join from a word problem. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Choosing the Right Join Type” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. CROSS JOIN and Cartesian Products
  2. SELF JOIN for Hierarchies
  3. Comparing Rows Within One Table
  4. Choosing the Right Join Type
← Back to SQL Interview Prep