JOINs & multi-table relationships

JOIN is where relational modeling becomes useful. A join combines rows according to a relationship condition. The critical skill is not memorizing join diagrams; it is reasoning about which rows should survive when a match exists or does not exist.

INNER JOIN

INNER JOIN keeps only matching pairs.

SELECT o.id AS order_id,
       c.name AS customer,
       o.total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

Run the query to see the result.

Notice that the sample order whose customer_id is 999 disappears because no matching customer exists.

LEFT JOIN

LEFT JOIN keeps every row from the left side and fills right-side columns with NULL when there is no match.

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

Run the query to see the result.

This reveals No Orders Ltd even though it has no order.

Finding missing relationships with an anti-join

SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

Run the query to see the result.

NOT EXISTS is another robust expression of the same intent and often scales well.

Filter placement changes outer-join meaning

With a LEFT JOIN, putting a condition on the right table in WHERE can remove the NULL-extended rows and effectively turn the query into an inner join. If the condition defines which right-side rows may match while unmatched left rows must remain, put it in ON.

SELECT c.id, c.name, o.id AS paid_order
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
ORDER BY c.id, o.id;

Run the query to see the result.

Many-to-many joins and duplicate amplification

Orders and products are connected through order_items. One order can have many products; one product can appear in many orders. Joining across that bridge creates one result row per matching relationship, which is correct but can surprise you if you expected one row per order.

SELECT o.id AS order_id,
       p.name AS product,
       oi.quantity,
       oi.unit_price
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
ORDER BY o.id, p.id;

Run the query to see the result.

Self joins

A self join treats one table as two logical roles. Typical use cases are organizational hierarchies, predecessor/successor relationships, and comparing rows inside the same entity set. Always use clear aliases.

RIGHT and FULL joins

PostgreSQL supports RIGHT JOIN and FULL OUTER JOIN. SQLite's support depends on version and the site's learning runner intentionally focuses on the common INNER/LEFT patterns. FULL joins are useful for reconciliation, but set operations can sometimes express QA comparisons more clearly.

Practice: find orders with no matching customer, then find customers with no orders. These are different questions and should produce different SQL.