Join related tables

Combine rows from related tables with explicit join conditions.

Why joins?

Related facts live in separate tables to avoid duplication and to keep relationships explicit. A join combines rows according to a condition—usually matching a foreign key to a primary key.

SELECT
    o.order_id,
    c.name AS customer_name,
    o.ordered_at,
    o.status
FROM orders AS o
JOIN customers AS c
    ON c.customer_id = o.customer_id
ORDER BY o.ordered_at DESC;

An inner JOIN includes only rows with a match on both sides. A LEFT JOIN keeps every row from its left input and fills unmatched right-side columns with NULL:

SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id;

That query can reveal customers with no orders. When filtering a LEFT JOIN, be mindful of whether a condition belongs in ON or WHERE: filtering a right-side column in WHERE can remove the unmatched rows and make the result behave like an inner join.

Join order items to products

SELECT
    oi.order_id,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM order_items AS oi
JOIN products AS p
    ON p.product_id = oi.product_id
WHERE oi.order_id = 42;

Qualify columns with table aliases in multi-table queries. This avoids ambiguity and makes each relationship visible. A many-to-many relationship generally requires joining through its linking table.

Exercise: Write a query that lists each order ID and its customer's email.

Sources: PostgreSQL: Joins Between Tables, PostgreSQL: Foreign Keys