SQL JOIN Interview Questions (With Examples)

Why interviewers ask this

Join questions look like syntax questions but are really testing something else: whether a candidate can predict, before running a query, what happens to rows that don't have a match on the other side. That single skill accounts for most real-world join bugs, and it's what this guide is built around.

The first four questions get full treatment: working query, the common mistake, and what changes if you get it wrong. The remaining six are tighter: one working example and the insight it tests.

How interviewers read a JOIN answer

Layer	What a weaker answer does	What a stronger answer does
Unmatched-row prediction	Assumes every row will have a match, doesn't consider what happens when one doesn't	States what happens to unmatched rows before writing the query
Filter placement	Puts filters on the joined table in `WHERE`, accidentally changing `LEFT JOIN` behaviour	Knows to filter in the `ON` clause when the join type needs to be preserved
Join selection	Defaults to `INNER JOIN` out of habit, regardless of what the question actually needs	Picks the join type based on which rows must be preserved, not by default
Communication	Writes the query and runs it without narrating the logic	States which table drives the query and what the join condition guarantees

1What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

SQL · inner_left_right_full_outer_join.sql

-- INNER JOIN: only rows with a match in both tables
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;


-- LEFT JOIN: all rows from orders, matched customer data where it exists
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;


-- RIGHT JOIN: all rows from customers, matched order data where it exists
SELECT o.order_id, c.name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;


-- FULL OUTER JOIN: all rows from both, matched where possible
-- (MySQL has no native FULL OUTER JOIN; emulate with UNION)
SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.id
UNION
SELECT o.order_id, c.name FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id;

Join type	Rows kept	Common use case
`INNER JOIN`	Only matched rows	You need fully matched data on both sides
`LEFT JOIN`	All left-table rows	You need every order, even ones with no customer record
`RIGHT JOIN`	All right-table rows	Rare; usually rewritten as a `LEFT JOIN` with tables swapped
`FULL OUTER JOIN`	All rows from both	You need to see gaps on either side

Evaluates: whether the candidate can map each join type to the rows it preserves, not just recite definitions. Most candidates can name all four; fewer can say which one to reach for given a specific requirement.

2Why does adding a WHERE filter sometimes turn a LEFT JOIN into an INNER JOIN by accident?

SQL · left_join_where_clause_bug.sql

-- Intended: all customers, with order info where it exists
-- Bug: WHERE filters out customers with no matching order at all
SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed';

Because o.status is NULL for customers with no orders, the WHERE clause excludes them entirely, even though the LEFT JOIN was written specifically to keep them. The fix moves the filter into the join condition:

SQL · left_join_fixed_with_on_clause.sql

SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed';

Now the filter only affects which order rows get matched, not whether the customer row survives at all.

Evaluates: this is one of the most common join bugs in real code, not just interviews. Recognising it (and knowing to filter in ON rather than WHERE when the join type needs to be preserved) is a strong, specific signal.

3How do you join more than two tables in a single query?

SQL · multi_table_join.sql

SELECT o.order_id, c.name AS customer, p.name AS product, o.quantity
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

Each join adds one more table and one more ON condition. The naive alternative nests subqueries for each lookup, which works for one extra table but becomes unreadable and slow past two or three:

SQL · multi_table_nested_subquery_alternative.sql

-- Works, but doesn't scale past a couple of lookups
SELECT o.order_id,
       (SELECT name FROM customers WHERE id = o.customer_id) AS customer
FROM orders o;

Evaluates: comfort chaining multiple joins in one query rather than defaulting to nested subqueries for every related table, which becomes both harder to read and harder for the database to optimize as more tables are added.

4What's the difference between CROSS JOIN and an accidental Cartesian product?

SQL · cross_join_vs_cartesian_product.sql

-- Intentional: every combination of size and colour
SELECT s.size, c.colour
FROM sizes s
CROSS JOIN colours c;


-- Accidental: missing ON condition produces the same result unintentionally
SELECT o.order_id, c.name
FROM orders o, customers c; -- no WHERE or ON linking them

The second query looks like a normal multi-table query but has no condition linking orders to customers, so every order gets paired with every customer. On a table with a thousand orders and a thousand customers, that's a million rows returned instead of the intended one-to-one match.

Evaluates: recognising that a missing join condition doesn't cause an error, it silently produces a much larger, wrong result set. This is one of the more expensive mistakes to catch after the fact.

5What's the difference between a JOIN and a UNION?

SQL · join_vs_union.sql

-- JOIN: combines columns from two tables, side by side
SELECT o.order_id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;


-- UNION: stacks rows from two queries with the same column structure
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Evaluates: knowing a JOIN combines columns across a row, while a UNION combines rows across queries with matching column structure. Confusing the two is a common beginner mistake.

6How do you find records in one table that don't exist in another?

SQL · records_not_in_other_table.sql

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

Evaluates: the LEFT JOIN ... WHERE ... IS NULL pattern, one of the standard ways to find unmatched rows, alongside NOT EXISTS.

7How do you find employees who work in the same department as each other?

SQL · employees_same_department_self_join.sql

SELECT e1.name AS employee1, e2.name AS employee2, e1.department
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department AND e1.id < e2.id;

Evaluates: self-join fluency, and specifically why e1.id < e2.id (rather than !=) is needed to avoid both duplicate pairs and an employee being paired with themselves.

8How do you get the total order count per customer, including customers with zero orders?

SQL · order_count_per_customer_left_join.sql

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

Evaluates: knowing that COUNT(o.order_id) (counting a column from the joined table) correctly returns 0 for unmatched rows, while COUNT(*) would incorrectly count 1 for every customer regardless of whether they have orders.

9What's the risk of using NATURAL JOIN instead of an explicit ON condition?

SQL · natural_join_risk.sql

-- Joins automatically on any columns with matching names
SELECT * FROM orders NATURAL JOIN customers;

Evaluates: understanding that NATURAL JOIN silently joins on every same-named column, which breaks unpredictably if either table's schema changes. Most experienced engineers avoid it in favour of an explicit ON condition for exactly this reason.

10What's the difference between USING and ON in a join condition?

SQL · using_vs_on_clause.sql

-- ON: explicit, works with any column names
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;


-- USING: shorthand, requires the column to share the exact same name in both tables
SELECT * FROM orders o JOIN customers c USING (customer_id);

Evaluates: knowing USING is a readability shortcut for the common case where both tables use the same column name, while ON is required whenever the column names differ or the join condition is more complex than a simple equality.

How should you prepare for a SQL JOIN interview?

Ask what happens to unmatched rows before writing anything. For every join question, state out loud which rows are guaranteed to survive and which might not, before you touch the keyboard.
Know the WHERE-versus-ON filter placement rule cold. It's the single most common join bug, and interviewers know it, which is why it comes up so often.
Practise self-joins separately. They look unfamiliar the first few times, since the same table plays two different roles in one query, but the pattern (alias the table twice, join on a relationship column) is identical every time.

Frequently asked questions

Who is this guide for, and what SQL experience does it assume? This guide is written for candidates who already know basic SELECT and WHERE and want to go deeper specifically on joins. If you're new to SQL entirely, get comfortable with single-table queries first.

Is RIGHT JOIN commonly used in practice? Rarely. Most engineers rewrite a RIGHT JOIN as a LEFT JOIN with the table order swapped, since it reads more naturally. Interviewers still ask about it to check whether a candidate understands it's logically equivalent, not a different capability.

Why doesn't MySQL support FULL OUTER JOIN natively? It's a deliberate omission in MySQL's implementation. The standard workaround is a UNION of a LEFT JOIN and a RIGHT JOIN between the same two tables, which is worth knowing cold since it comes up as a follow-up question.

What's the most common join mistake in real production code, not just interviews? Filtering a LEFT JOIN's right-hand table in the WHERE clause instead of the ON clause, which silently turns it into an INNER JOIN and drops rows the query was specifically written to keep.

How is this different from the general SQL interview guide? The Top SQL Interview Questions guide spans joins, aggregation, subqueries, and window functions broadly. This guide focuses specifically on join types and the mistakes that come with them.