Combine rows from related tables — INNER JOIN to keep matches, LEFT/RIGHT/FULL joins to keep unmatched rows too, plus self joins and cross joins, all driven by a foreign key relationship.
Why: good database design splits data across tables to avoid repeating it. Users live in "users", their purchases in "orders", and each order stores a user_id that points back to its user — the foreign key you already created in lesson 2. A JOIN stitches those rows back together when you query. Note: you do not re-create orders here; instead inspect the existing table to see that foreign key.
-- describe the orders table; look at the "Foreign-key constraints" line
\d ordersWhy: an INNER JOIN returns only rows that have a match on both sides — every order paired with the user who placed it. Orders with no matching user, and users with no orders, are left out. Note: "o" and "u" are short aliases so you do not retype the full table names.
SELECT o.id, u.full_name, o.total
FROM orders o
INNER JOIN users u ON u.id = o.user_id;Why: a LEFT JOIN keeps every row from the left (first) table even when there is no match on the right — the missing columns come back as NULL. This is how you answer "show all users, including ones who never ordered".
SELECT u.full_name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- users with no orders still appear, with order_id = NULLWhy: RIGHT JOIN keeps every row from the right table (the mirror image of LEFT JOIN), and FULL JOIN keeps unmatched rows from both sides. Combined with "WHERE … IS NULL" they let you find rows that have no partner.
-- every order, even any whose user row was deleted
SELECT o.id, u.full_name
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;-- find users with no orders
SELECT u.full_name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;Why: a self join joins a table to itself — useful when rows reference other rows in the same table, like an employee whose manager_id points at another employee. A CROSS JOIN pairs every row with every row (rarely what you want, but handy for generating combinations).
-- each employee next to their manager's name
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;