Joins — INNER, LEFT, RIGHT, FULL
Combine rows across tables using keys. Pick the right join type or you'll silently lose or duplicate data.
A join combines rows from two (or more) tables based on a relationship — usually a match. Without joins, you'd have one giant denormalized table.
INNER JOIN: returns only rows where the join condition matches in both tables. Customers who have at least one order.
LEFT JOIN (aka LEFT OUTER JOIN): returns ALL rows from the left table, plus matching rows from the right ( where no match). Customers with orders if any, customers with NO orders show with NULL order fields. Essential for 'show me customers who haven't ordered yet' queries.
RIGHT JOIN: mirror of LEFT. Rarely used — people flip the table order and use LEFT. Readable > clever.
FULL OUTER JOIN: all rows from both sides, NULL where there's no match. Useful for 'what's on A that's not on B AND vice versa'.
CROSS JOIN: every row from A paired with every row from B — . 10 customers × 5 products = 50 rows. Useful for generating combinations (e.g., fill a calendar matrix), explosive if accidentally applied to big tables.
Join trap: if the join key isn't unique on one side, you duplicate rows. Customer joins orders 1-to-many → one customer with 5 orders appears 5 times. If you then SUM(customer.lifetime_value), you over-count 5×.
Diagram
Grounded on https://www.postgresql.org/docs/current/queries-table-expressions.html
Next up
GROUP BY & aggregates
Roll up rows into buckets and summarize: COUNT, SUM, AVG, MIN, MAX. The foundation of every report.