SQLite JOINs: Query Related Tables

Match orders to customers

An INNER JOIN returns rows with a match on both sides. Joining the order’s customer_id to the customer’s id connects a purchase to its buyer.

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

Keep customers without orders

A LEFT JOIN keeps every customer, even when there is no matching order. Count the order’s id rather than COUNT(*) so a customer without orders gets zero.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id, c.name;

Avoid multiplying totals

Joining an order to its individual order_items repeats the order total for every item. Summing orders.total after that join can overcount revenue. Sum quantity multiplied by unit_price at item level, or aggregate each table before joining.

SELECT p.name, SUM(i.quantity) AS units_sold
FROM order_items AS i
JOIN products AS p ON p.id = i.product_id
GROUP BY p.id, p.name
ORDER BY units_sold DESC;

Join your own CSVs

Import related CSV files into the same workspace, then join their key columns. Verify that the keys use compatible representations: the text 00123 may intentionally differ from 123. Check for duplicate keys before assuming the join is one-to-one.