JOINs: INNER, LEFT, RIGHT, FULL

Joins are how SQL truly earns its keep. They let you ask questions that span multiple tables — and most real questions do.

Picture two tables: customers and orders. Each customer has many orders. To get 'each order with the customer's name attached', you JOIN them on customer_id.

INNER JOIN

SELECT o.id, o.total_amount, c.first_name, c.country
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Returns one row per order, with the matching customer's columns alongside. The ON clause says 'match orders to customers where the customer_id columns are equal'. Aliases (o, c) are optional but make joins much more readable.

LEFT JOIN

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

Keeps every customer, even those with no orders (their order_count is 0). LEFT JOIN means: keep all rows from the left table; fill matching right-table columns with NULL where there's no match.

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is the mirror of LEFT — keep all of the right table. Most analysts never write RIGHT JOIN; they swap the table order and use LEFT instead because it reads more naturally.

FULL OUTER JOIN keeps every row from both tables, NULL-padding non-matches on either side. Useful for set comparisons (which records are in A but not B?).

Multiple joins

SELECT o.id, c.first_name, p.name AS product_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id;

Daisy-chain as many JOINs as needed. Each JOIN adds another table and an ON condition. Performance falls off if you have 10+ joins or join two huge tables without indexes — we cover that in module 10.