SQL Joins Interview Questions With Answers

Q1: What happens without an ON clause?

Question: what does SELECT * FROM customers, orders; return, given 3 customers and 4 orders?

With no join condition, every customer row is paired with every order row — a cartesian product. It's valid SQL, it runs without error, and it silently produces far more rows than anyone intended, which is exactly why it's a favorite interview question.

Q2: Write a self-join to compare rows in the same table

Question: find every employee who earns more than their own manager.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

The same table plays two roles at once — e for "the employee being checked" and m for "that employee's manager" — joined through manager_id pointing back into the same table's id column. Without two distinct aliases, the database has no way to tell the two roles apart.

Q3: Why did a JOIN return more rows than expected?

customers: 1 row (Alice)
orders: 3 rows, all belonging to Alice

Query: SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id;

This is fan-out, and it's completely correct behavior, not a bug: joining a "one" side to a "many" side produces one output row per match, so Alice legitimately appears three times, once per order. Fan-out compounds with every additional one-to-many join in the same query, which is why a SUM() computed after joining several one-to-many tables together can be dramatically inflated.

Q4: Simulate a FULL OUTER JOIN without native support

Some databases (notably older MySQL versions) don't support FULL OUTER JOIN directly. The standard workaround combines a LEFT JOIN and a RIGHT JOIN with UNION, which deduplicates the rows that both sides return in common:

SELECT c.name, o.total FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.total FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

UNION, not UNION ALL, matters here — it removes the rows both queries produce in common (the actual matches), leaving exactly the matched rows once, plus the unmatched customers and unmatched orders each exactly once.

Q5: ON condition vs WHERE condition on an outer join

Question: for a LEFT JOIN, does it matter whether a filter on the right table goes in ON or WHERE?

Yes — dramatically. Moving the condition into ON applies it during the join, so unmatched left rows are still preserved with NULL. Moving it to WHERE applies it after the join, and NULL = 'shipped' filters those rows out, silently converting a LEFT JOIN into the equivalent of an INNER JOIN. See SQL Output Based Interview Questions for the same trap worked through in full.

Q6: INNER, LEFT, RIGHT, FULL — the four in one table

For the full breakdown with Venn diagrams and sample data, see INNER JOIN vs LEFT JOIN vs RIGHT JOIN.