Explain INNER, LEFT, RIGHT and FULL OUTER joins with an example.

A join combines rows from two tables based on a condition. The join type decides what happens to rows that do not have a match:

• INNER JOIN: only rows that match in both tables.
• LEFT (OUTER) JOIN: all rows from the left table, plus matching right rows; where there is no match, the right-side columns are NULL.
• RIGHT (OUTER) JOIN: the mirror image; all rows from the right table. In practice most people rewrite it as a LEFT JOIN with the tables swapped.
• FULL OUTER JOIN: all rows from both tables, matched where possible, NULLs on whichever side is missing. (Not supported in MySQL, which needs a UNION of LEFT and RIGHT joins.)
• CROSS JOIN: every combination (Cartesian product), rarely what you want in a test query.

Example data

-- customers
-- id | name
--  1 | Asha
--  2 | Ben
--  3 | Chen

-- orders
-- id  | customer_id | total
-- 101 | 1           | 40.00
-- 102 | 1           | 15.00
-- 103 | 2           | 22.50
-- 104 | 9           | 10.00   -- customer 9 does not exist

INNER JOIN

SELECT c.name, o.id AS order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;
-- Asha 101, Asha 102, Ben 103   (Chen and order 104 disappear)

LEFT JOIN

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Asha 101, Asha 102, Ben 103, Chen NULL

Note that a one-to-many join repeats the left row for each match. Asha appears twice, which matters when you SUM or COUNT afterwards.

FULL OUTER JOIN

SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
-- Asha 101, Asha 102, Ben 103, Chen NULL, NULL 104

Anti-join: finding rows with no match

This is the join a tester uses most:

-- Customers who never ordered
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Orders pointing at a non-existent customer (orphans)
SELECT o.*
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);

A classic pitfall: WHERE versus ON

-- Intended: all customers, with their paid orders if any
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';          -- removes customers with no orders: now behaves like INNER JOIN

-- Correct: put the right-table filter in ON
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

The WHERE condition is evaluated after the join, and NULL = 'paid' is not true, so the unmatched rows are filtered out.

What interviewers look for

• Correct definitions, especially what happens to unmatched rows.
• The anti-join pattern for finding missing or orphaned data.
• Awareness of row multiplication in one-to-many joins.
• The WHERE versus ON pitfall with outer joins.