Q3 of 26 · SQL
What is the difference between INNER JOIN and LEFT JOIN, and when would you use each in a QA context?
Short answer
Short answer: INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table, with NULLs where there is no match — essential for finding records that are missing a related row.
Detail
INNER JOIN — gives you the intersection. Use it when you only care about records that have a corresponding row in the joined table.
-- Orders that have a payment record
SELECT o.order_id, p.payment_status
FROM orders o
INNER JOIN payments p ON p.order_id = o.order_id;
LEFT JOIN — keeps every row from the left table, filling right-side columns with NULL when no match exists. This is the pattern for finding orphaned or missing records:
-- Orders with NO payment record (orphaned orders)
SELECT o.order_id, o.created_at
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.order_id IS NULL;
RIGHT JOIN — mirror of LEFT JOIN (less common; most engineers just flip the table order and use LEFT JOIN).
FULL OUTER JOIN — all rows from both sides, NULLs where no match. Useful for comparing two datasets to find rows missing from either side (e.g., comparing a source table against a target table after a migration).
// EXAMPLE
-- INNER: only orders that have payments
SELECT o.order_id, p.amount
FROM orders o
INNER JOIN payments p ON p.order_id = o.order_id;
-- LEFT: all orders; NULL payment_id means no payment row exists
SELECT o.order_id, p.payment_id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id;
-- Find orphaned orders (no payment row)
SELECT o.order_id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL;