Joining tables

Below, orders gets an alias o and customers gets c - short nicknames so you can write o.order_id and c.name without typing the full table name each time. The ON clause names the shared key. Press ▶ Run and watch order rows arrive with a real name attached.

SELECT o.order_id, c.name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LIMIT 10;

Aliases feel optional on a two-table join and become essential the moment you have three tables or two columns with the same name (both orders and customers have a customer_id). Prefixing every column with its table - o.customer_id vs c.customer_id - removes all ambiguity and makes the query readable at a glance.

• Disambiguate: when two tables share a column name, an unqualified name is an error. o.customer_id says exactly which one.
• Readability: c.name reads faster than customers.name, especially across three joins.
• Convention: a one- or two-letter alias near the table name (o for orders, oi for order_items) is standard across every SQL shop.

Here is the payoff. A LEFT JOIN from customers keeps every customer even if they have no orders. COUNT(o.order_id) counts only real order rows - so a customer with no match counts as 0. Sort ascending and the quiet, never-ordered customers float to the top.

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

SQLite also supports RIGHT JOIN and FULL OUTER JOIN (added in SQLite 3.39). A RIGHT JOIN is just a LEFT JOIN with the tables flipped - it keeps every row from the right table. A FULL OUTER JOIN keeps every row from both sides, filling NULL wherever either side has no match.

• RIGHT JOIN: rarely needed - most people just reorder the tables and use LEFT. Same result, easier to read top to bottom.
• FULL OUTER JOIN: useful for reconciliation - "show me every customer and every order, matched where possible, gaps on both sides shown as NULL".
• In practice: INNER and LEFT cover the overwhelming majority of real work. Know RIGHT and FULL exist; reach for them when a reconciliation actually calls for them.

Read the joins as a path: customers → orders (on customer_id) → order_items (on order_id). Line revenue is quantity * unit_price; SUM rolls it up per customer. We filter to status = 'completed' so refunds and cancels do not inflate the numbers.

SELECT c.name, ROUND(SUM(oi.quantity*oi.unit_price),2) AS spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status='completed'
GROUP BY c.customer_id
ORDER BY spent DESC
LIMIT 5;

A self-join is a table joined to a second copy of itself, using two different aliases. It answers "which rows relate to other rows in the same table" - here, pairs of customers who live in the same city. Give customers two aliases (a and b) and match them on city.

SELECT a.name AS customer_a, b.name AS customer_b, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.customer_id < b.customer_id
ORDER BY a.city;

The a.customer_id < b.customer_id condition keeps each pair once and stops a row from matching itself. Self-joins power "friends in the same region", "duplicate detection", and org-chart "who reports to whom" style questions.