SQL joins and indexes

Two ideas carry most of everyday SQL work: combining tables with joins, and making queries fast with indexes.

Example tables

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, created_at TEXT);

Inner join

An inner join returns only rows that have a match on both sides.

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Customers without orders do not appear.

Left join

A left join keeps every row from the left table. Where there is no match, the right side’s columns are NULL.

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

This lists customers with zero orders too. Note COUNT(o.id) rather than COUNT(*): COUNT(*) would count the single NULL-filled row as 1.

A common trap: putting a condition on the right table in WHERE turns a left join back into an inner join, because NULL never satisfies it. Put it in the ON clause instead.

-- Drops customers with no 2026 orders (probably not what you meant)
SELECT c.name, o.total FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01';

-- Keeps every customer, with NULLs where they had no 2026 orders
SELECT c.name, o.total FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.created_at >= '2026-01-01';

How indexes speed things up

Without an index, finding orders WHERE customer_id = 42 means reading every row: a full table scan, O(n). A B-tree index keeps the column’s values sorted in a balanced tree, so the database finds matching rows in O(log n) steps and then fetches just those rows.

CREATE INDEX orders_customer ON orders (customer_id);

Indexes are not free. Every insert, update and delete must also update each index, and indexes use storage. Index the columns you filter, join and sort on, not every column.

Composite indexes and column order

An index on several columns is sorted by the first column, then by the second within it, and so on, like a phone book sorted by last name and then first name.

CREATE INDEX orders_customer_date ON orders (customer_id, created_at);

This index serves:

WHERE customer_id = 42
WHERE customer_id = 42 AND created_at >= '2026-01-01'
WHERE customer_id = 42 ORDER BY created_at

It does not help WHERE created_at >= '2026-01-01' alone, just as a phone book sorted by last name does not help you find everyone named “Ana”. Put equality-filtered columns first and range or sort columns after them.

Reading the plan

Ask the database how it will run a query before guessing.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;

In SQLite, SCAN orders means a full scan, SEARCH orders USING INDEX orders_customer_date (customer_id=?) means the index is used, and USE TEMP B-TREE FOR ORDER BY means the result is sorted separately because no index provides the order. PostgreSQL’s EXPLAIN ANALYZE goes further: it runs the query and reports actual row counts and timings for each step.