Joins
INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
Skills taught: sql.inner-join, sql.left-join, sql.right-join, sql.full-join, sql.join-conditions
INNER JOIN — only matching rows
-- Get order details WITH user names
-- Without join: orders only has user_id (a number), not the name
SELECT
orders.id AS order_id,
users.name AS customer,
orders.total,
orders.ordered_at
FROM orders
INNER JOIN users ON orders.user_id = users.id;
-- Shorter with aliases
SELECT
o.id AS order_id,
u.name AS customer,
o.total,
o.ordered_at
FROM orders o
JOIN users u ON o.user_id = u.id; -- JOIN = INNER JOIN
-- Join all three tables
SELECT
u.name AS customer,
p.name AS product,
o.quantity,
o.total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
ORDER BY o.ordered_at;
LEFT JOIN — keep all left rows
-- LEFT JOIN: all users, even those with no orders
-- Users without orders get NULL for order columns
SELECT
u.name,
COUNT(o.id) AS order_count,
SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;
-- Find users who have NEVER placed an order
-- (rows where the right table produced NULL)
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- o.id is NULL = no matching order
Frequently Asked Questions
What happens if I forget the ON condition?
Without ON, you get a CROSS JOIN (Cartesian product) — every row from the left table matched with every row from the right. 5 users × 6 products = 30 rows. This is almost never what you want and can produce millions of rows on large tables.
How do I find rows in table A that have no matching row in table B?
Use LEFT JOIN + WHERE right_table.primary_key IS NULL. Example: SELECT u.name FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL — users who have never ordered. This is called an 'anti-join.'