🎓 Velnov

🗄️ SQL · Topic 12 of 20

OUTER JOINs — SQL Questions with Answers

30 practice questions on OUTER JOINs, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

1 easy · 14 medium · 15 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

LEFT JOINOuter joinsDirectionRIGHT JOINFULL OUTER JOINAnti-joinNULLON vs WHERECOUNTGotchasEmpty tablesReconciliationOrderJoin chainsCOALESCESUMOptional dataMySQLTerminologyNOT EXISTSRow countsStyleReadabilityFan-out

Jump to: Easy · Medium · Hard

Easy questions (1)

Q1. easy

What does a LEFT JOIN return?

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
  1. A ALL rows from the left table, plus matching right-table columns (NULL where no match)
  2. B Only matching rows
  3. C All rows from the right table
  4. D Rows with no match only

Answer: A — ALL rows from the left table, plus matching right-table columns (NULL where no match)

LEFT JOIN preserves every left row. Customers with no orders still appear, with the order columns showing NULL - which INNER JOIN would have dropped.

LEFT JOINOuter joins

Medium questions (14)

Q2. medium

In 'orders LEFT JOIN customers', which table keeps all its rows?

  1. A orders - the LEFT (first) table
  2. B customers - the right table
  3. C Both tables
  4. D Neither

Answer: A — orders - the LEFT (first) table

LEFT JOIN keeps everything from the table on the left of the keyword. Every order appears, even those whose customer_id has no matching customer.

LEFT JOINDirection

Q3. medium

What does RIGHT JOIN return?

  1. A All rows from the RIGHT table, plus matching left-table columns (NULL where no match)
  2. B Only matches
  3. C All left rows
  4. D Nothing

Answer: A — All rows from the RIGHT table, plus matching left-table columns (NULL where no match)

RIGHT JOIN is the mirror of LEFT JOIN. In practice most people rewrite RIGHT joins as LEFT joins by swapping table order, because left-to-right reads more naturally.

RIGHT JOIN

Q5. medium

What does FULL OUTER JOIN return?

  1. A ALL rows from BOTH tables - matched where possible, NULLs filled in where not
  2. B Only matching rows
  3. C Only unmatched rows
  4. D The left table only

Answer: A — ALL rows from BOTH tables - matched where possible, NULLs filled in where not

FULL OUTER JOIN is the union of LEFT and RIGHT: every unmatched row from either side is kept. (MySQL lacks it natively; emulate with LEFT JOIN UNION RIGHT JOIN.)

FULL OUTER JOIN

Q10. medium

What does this count? SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name;

  1. A Every customer's order count, showing 0 for customers with no orders
  2. B Only customers with orders
  3. C Total orders
  4. D An error

Answer: A — Every customer's order count, showing 0 for customers with no orders

COUNT(o.id) counts non-NULL order ids, so unmatched customers correctly show 0. Note COUNT(*) would show 1 for them (the single NULL-padded row), which is a subtle trap.

LEFT JOINCOUNT

Q12. medium

What does a LEFT JOIN return when the left table is empty?

  1. A Zero rows - there are no left rows to preserve
  2. B All right rows
  3. C One NULL row
  4. D An error

Answer: A — Zero rows - there are no left rows to preserve

LEFT JOIN preserves LEFT rows; with none, the result is empty regardless of the right table's contents.

LEFT JOINEmpty tables

Q14. medium

Does the ORDER of tables matter for LEFT JOIN?

  1. A Yes - LEFT JOIN is NOT symmetric; A LEFT JOIN B differs from B LEFT JOIN A
  2. B No, it is symmetric
  3. C Only for three tables
  4. D Only with WHERE

Answer: A — Yes - LEFT JOIN is NOT symmetric; A LEFT JOIN B differs from B LEFT JOIN A

The left table's rows are always preserved, so swapping the tables changes which side keeps its unmatched rows. This is a key difference from INNER JOIN.

LEFT JOINOrder

Q16. medium

How do you show all products and their total sales, INCLUDING never-sold products?

SELECT p.title, COALESCE(SUM(i.quantity), 0) AS sold
FROM products p
LEFT JOIN order_items i ON p.id = i.product_id
GROUP BY p.title;
  1. A LEFT JOIN from products to order_items, then SUM with COALESCE for zero
  2. B INNER JOIN products to order_items
  3. C RIGHT JOIN order_items
  4. D CROSS JOIN

Answer: A — LEFT JOIN from products to order_items, then SUM with COALESCE for zero

Starting from products and LEFT JOINing sales keeps every product. Wrap the sum: COALESCE(SUM(i.quantity), 0) so unsold products show 0 rather than NULL.

LEFT JOINCOALESCE

Q18. medium

What is a common real-world use of LEFT JOIN?

  1. A Listing all items and their optional related data - e.g. all users with their last login (or none)
  2. B Deleting orphan rows
  3. C Speeding up queries
  4. D Sorting tables

Answer: A — Listing all items and their optional related data - e.g. all users with their last login (or none)

Whenever the related data is optional - a profile photo, a last order, a manager - LEFT JOIN ensures the main entity is not dropped just because the optional part is missing.

LEFT JOINOptional data

Q20. medium

What does the term 'outer' mean in outer join?

  1. A Rows OUTSIDE the matched set (unmatched rows) are preserved, unlike inner join
  2. B It joins external databases
  3. C It refers to the outermost query
  4. D Nothing specific

Answer: A — Rows OUTSIDE the matched set (unmatched rows) are preserved, unlike inner join

INNER keeps only the intersection; OUTER additionally keeps the 'outer' unmatched rows from one side (LEFT/RIGHT) or both (FULL).

Outer joinsTerminology

Q22. medium

What appears in the right-table columns for an unmatched LEFT JOIN row?

  1. A NULL
  2. B 0
  3. C Empty string
  4. D The default value

Answer: A — NULL

Every right column is NULL when there is no match. This is why you test for NULL to find unmatched rows, and use COALESCE to supply display defaults.

NULLLEFT JOIN

Q24. medium

Can you LEFT JOIN more than two tables?

  1. A Yes - chain them, keeping in mind each LEFT preserves rows accumulated so far
  2. B No, only two
  3. C Only with FULL join
  4. D Only in subqueries

Answer: A — Yes - chain them, keeping in mind each LEFT preserves rows accumulated so far

Multiple LEFT JOINs are common (a user, their optional profile, their optional settings). Be careful that a later INNER JOIN does not filter out preserved rows.

Join chainsLEFT JOIN

Q26. medium

Which is clearer to read: LEFT JOIN or RIGHT JOIN?

  1. A LEFT JOIN - reading left to right, the 'main' table comes first
  2. B RIGHT JOIN always
  3. C They are equally clear
  4. D FULL JOIN

Answer: A — LEFT JOIN - reading left to right, the 'main' table comes first

Convention favours LEFT JOIN so the preserved (main) table leads the FROM clause. Consistent direction makes complex queries easier to follow.

StyleReadability

Q28. medium

What does COALESCE(o.status, 'no orders') do in a LEFT JOIN?

  1. A Displays 'no orders' for unmatched customers instead of NULL
  2. B Filters out NULLs
  3. C Counts orders
  4. D Sorts the result

Answer: A — Displays 'no orders' for unmatched customers instead of NULL

COALESCE gives friendly labels to the NULLs that outer joins produce - much nicer in a report than blank cells.

COALESCELEFT JOIN

Q30. medium

When should you choose LEFT JOIN over INNER JOIN?

  1. A When you must keep ALL rows of the main table even if the related data is missing
  2. B Always
  3. C Never - INNER is always better
  4. D Only for two tables

Answer: A — When you must keep ALL rows of the main table even if the related data is missing

If missing related data should hide the main row, use INNER. If the main row must always appear (all customers, all products), use LEFT. Choosing correctly is the heart of join design.

LEFT JOININNER JOINDesign

Hard questions (15)

Q4. hard

How can any RIGHT JOIN be rewritten?

  1. A As a LEFT JOIN with the two tables swapped
  2. B As an INNER JOIN
  3. C As a CROSS JOIN
  4. D It cannot be rewritten

Answer: A — As a LEFT JOIN with the two tables swapped

A RIGHT JOIN B equals B LEFT JOIN A. Because LEFT joins are more intuitive, many teams standardise on them and avoid RIGHT joins entirely.

LEFT JOINRIGHT JOIN

Q6. hard

How do you find customers who have placed NO orders (an anti-join)?

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
  1. A LEFT JOIN orders and keep rows WHERE o.id IS NULL
  2. B INNER JOIN and count zero
  3. C RIGHT JOIN customers
  4. D It is impossible

Answer: A — LEFT JOIN orders and keep rows WHERE o.id IS NULL

A LEFT JOIN gives NULLs for unmatched right rows; filtering for those NULLs finds the 'orphans'. This is the anti-join pattern - a very common interview question.

  • Filter on the right table's PRIMARY KEY being NULL for reliability.

Anti-joinLEFT JOINNULL

Q7. hard

In an anti-join, which column should you test for IS NULL?

  1. A The right table's PRIMARY KEY (or a NOT NULL column) - a nullable column could be NULL even when matched
  2. B Any column works
  3. C The left table's key
  4. D The join column of the left table

Answer: A — The right table's PRIMARY KEY (or a NOT NULL column) - a nullable column could be NULL even when matched

If you test a nullable right column, a matched row that happens to store NULL there would be falsely flagged as unmatched. The primary key is never NULL in a real match.

Anti-joinNULL

Q8. hard

Why does putting a right-table filter in WHERE 'break' a LEFT JOIN? LEFT JOIN orders o ON ... WHERE o.status = 'paid'

LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'paid'
  1. A The WHERE removes the NULL rows, silently turning the LEFT JOIN into an INNER JOIN
  2. B It causes a syntax error
  3. C It has no effect
  4. D It duplicates rows

Answer: A — The WHERE removes the NULL rows, silently turning the LEFT JOIN into an INNER JOIN

Unmatched left rows have o.status = NULL, and NULL = 'paid' is not TRUE, so WHERE discards them. To keep them, move the condition into the ON clause instead.

  • A subtle, very common bug.

LEFT JOINON vs WHERE

Q9. hard

What is the difference between putting a condition in ON vs WHERE for a LEFT JOIN?

  1. A ON filters which rows MATCH (unmatched left rows are still kept); WHERE filters the final result (can drop unmatched rows)
  2. B No difference
  3. C ON is only for inner joins
  4. D WHERE runs first

Answer: A — ON filters which rows MATCH (unmatched left rows are still kept); WHERE filters the final result (can drop unmatched rows)

For INNER JOIN the placement is equivalent, but for OUTER joins it changes the result fundamentally. Conditions on the outer (right) table usually belong in ON.

ON vs WHEREOuter joins

Q11. hard

Why use COUNT(o.id) instead of COUNT(*) in a LEFT JOIN aggregate?

  1. A COUNT(*) counts the NULL-padded row as 1; COUNT(o.id) counts only real matches, giving 0 for no orders
  2. B They are identical
  3. C COUNT(*) is faster and correct
  4. D COUNT(o.id) is invalid

Answer: A — COUNT(*) counts the NULL-padded row as 1; COUNT(o.id) counts only real matches, giving 0 for no orders

A customer with no orders still produces one result row (all order columns NULL). COUNT(*) counts that row; COUNT of a specific order column ignores it. Getting this wrong overstates counts by one.

COUNTNULLGotchas

Q13. hard

How do you find rows that exist in EITHER table but not both (symmetric difference)?

  1. A FULL OUTER JOIN with WHERE a.id IS NULL OR b.id IS NULL
  2. B INNER JOIN
  3. C CROSS JOIN
  4. D LEFT JOIN only

Answer: A — FULL OUTER JOIN with WHERE a.id IS NULL OR b.id IS NULL

FULL join keeps unmatched rows from both sides; filtering for NULL on either key leaves only the non-overlapping rows. Useful for reconciling two data sources.

FULL OUTER JOINReconciliation

Q15. hard

In a chain of LEFT JOINs, why can a later INNER JOIN undo earlier ones?

  1. A An INNER JOIN after LEFT JOINs discards rows that had NULLs, effectively filtering out the preserved rows
  2. B It never happens
  3. C INNER always comes first
  4. D Chained joins are invalid

Answer: A — An INNER JOIN after LEFT JOINs discards rows that had NULLs, effectively filtering out the preserved rows

Once you switch to INNER later in the chain, any row lacking a match there is dropped - including the NULL-padded rows the LEFT JOIN kept. Keep the chain LEFT if you must preserve rows throughout.

Join chainsOuter joins

Q17. hard

Why might SUM over a LEFT JOIN return NULL for unmatched rows?

  1. A There are no values to sum, and SUM of nothing is NULL - wrap with COALESCE(SUM(x), 0)
  2. B SUM always errors on joins
  3. C LEFT JOIN removes the row
  4. D It returns 0 automatically

Answer: A — There are no values to sum, and SUM of nothing is NULL - wrap with COALESCE(SUM(x), 0)

The unmatched right columns are all NULL, so SUM has nothing to add and yields NULL. COALESCE converts it to a numeric 0 for clean reports.

SUMNULLCOALESCE

Q19. hard

Does MySQL support FULL OUTER JOIN?

SELECT * FROM a LEFT JOIN b ON a.id=b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id=b.a_id;
  1. A Not directly - you emulate it with LEFT JOIN UNION RIGHT JOIN
  2. B Yes, fully
  3. C Only in views
  4. D Only with indexes

Answer: A — Not directly - you emulate it with LEFT JOIN UNION RIGHT JOIN

PostgreSQL, SQL Server and Oracle support FULL OUTER JOIN. In MySQL, combine a LEFT JOIN and a RIGHT JOIN with UNION to get the same result.

FULL OUTER JOINMySQL

Q21. hard

How would you list departments and their employee count, showing empty departments too?

  1. A SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON d.id = e.dept_id GROUP BY d.name;
  2. B INNER JOIN departments and employees
  3. C RIGHT JOIN employees
  4. D Count employees only

Answer: A — SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON d.id = e.dept_id GROUP BY d.name;

LEFT JOIN from departments preserves every department; COUNT(e.id) yields 0 for empty ones. INNER JOIN would silently hide departments with no staff.

LEFT JOINCOUNT

Q23. hard

Which join finds products that have NEVER been ordered?

  1. A products LEFT JOIN order_items ... WHERE order_items.id IS NULL
  2. B products INNER JOIN order_items
  3. C products RIGHT JOIN order_items
  4. D products CROSS JOIN order_items

Answer: A — products LEFT JOIN order_items ... WHERE order_items.id IS NULL

The anti-join again: keep all products, then filter to those with no matching order line. Equivalent to WHERE NOT EXISTS (SELECT 1 FROM order_items ...).

Anti-joinNOT EXISTS

Q25. hard

What is the row count of A LEFT JOIN B when every A row matches exactly one B row, plus 5 A rows match none?

  1. A The number of A rows (matched rows once each, plus the 5 unmatched once each)
  2. B A rows minus 5
  3. C A times B
  4. D Only the matched rows

Answer: A — The number of A rows (matched rows once each, plus the 5 unmatched once each)

One-to-one matches produce one row each, and unmatched A rows also produce one (NULL-padded) row each - so the total equals the A row count. Fan-out only occurs with one-to-many matches.

LEFT JOINRow counts

Q27. hard

How does a LEFT JOIN interact with a one-to-many relationship?

  1. A Matched left rows still fan out to multiple rows; unmatched left rows appear once with NULLs
  2. B It prevents fan-out
  3. C It always returns one row per left row
  4. D It errors

Answer: A — Matched left rows still fan out to multiple rows; unmatched left rows appear once with NULLs

LEFT JOIN does not stop duplication - a customer with 3 orders still yields 3 rows. It only guarantees that customers with 0 orders are not lost.

Fan-outLEFT JOIN

Q29. hard

Why can a LEFT JOIN sometimes be SLOWER than an INNER JOIN?

  1. A It must preserve unmatched rows, giving the optimiser fewer ways to prune and reorder
  2. B It is always slower
  3. C It scans twice
  4. D It never differs

Answer: A — It must preserve unmatched rows, giving the optimiser fewer ways to prune and reorder

INNER joins let the optimiser drop non-matching rows early and choose join order freely. Outer joins constrain this, so use INNER when you genuinely only need matches.

PerformanceOptimiser

Practice “OUTER JOINs” as a quiz