SQL: Joins, Indexes and Execution Plans

Real-world analogy

What is it?

A SQL join combines rows from two tables by a matching column. An index is a separate sorted structure that lets the database find rows without reading the whole table. The execution plan is the database's own report of how it ran your query: which indexes it used, whether it seeked or scanned, and how many rows it expected versus found. Knowing how to read it is how you move from "the query is slow" to "this exact step is slow, and here is the fix".

Real-world relevance

A report that ran in half a second on a small table takes a minute in production. The developer opens the actual execution plan, sees a scan on a table with millions of rows, and finds a function wrapped around the filter column. Rewriting the filter as a range and adding one covering index brings it back under a second. The code change is one line. Finding it required the plan.

Key points

• INNER vs LEFT join — INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and fills the missing right side with NULL.
• LEFT quietly becomes INNER — A WHERE filter on the right table, such as o.Status = 'Paid', drops the NULL rows. Put that condition in the ON clause to keep the LEFT behaviour.
• An index is a sorted lookup — The database keeps a second sorted copy of a column with pointers to the rows. A lookup becomes a quick search instead of a read of the whole table.
• Clustered vs non-clustered — In SQL Server the clustered index is the table itself in key order, so there is at most one. Non-clustered indexes are separate structures that point back to it. PostgreSQL tables are unordered heaps, so every PostgreSQL index works like a non-clustered one.
• Column order matters — A composite index on (CustomerId, CreatedAt) serves seeks on CustomerId, or on both. It cannot seek on CreatedAt alone, because entries are sorted by CustomerId first.
• Covering indexes with INCLUDE — INCLUDE (Total) stores extra columns in the index leaf, in SQL Server and PostgreSQL 11 or newer. When the index holds every column a query needs, the database can skip the trip back to the table.
• Non-sargable means no seek — A function on the column, like YEAR(CreatedAt), or a leading wildcard, like LIKE '%son', usually forces a scan. Compare the bare column to a range or a prefix instead.
• Reading an execution plan — A seek jumps to matching rows, and a scan reads them all. A SQL Server key lookup means the index lacked a needed column. Large gaps between estimated and actual rows often point to stale statistics.
• Indexes are not free — Each index uses disk space and must be updated when rows are inserted, deleted or change an indexed column. Add the ones your real queries need, not one per column.

Code example

-- SQL Server (T-SQL). Id is the clustered primary key; CreatedAt is datetime2.

-- Non-sargable: a function on the column forces a scan
SELECT o.Id, o.Total
FROM dbo.Orders AS o
WHERE YEAR(o.CreatedAt) = 2025;

-- Sargable: the bare column against a range can use an index seek
SELECT o.Id, o.Total
FROM dbo.Orders AS o
WHERE o.CreatedAt >= '2025-01-01' AND o.CreatedAt < '2026-01-01';

-- The index that serves it; INCLUDE makes it covering for this query
CREATE NONCLUSTERED INDEX IX_Orders_CreatedAt
    ON dbo.Orders (CreatedAt)
    INCLUDE (Total);

-- LEFT JOIN: Status in WHERE would turn this into an INNER JOIN
SELECT c.Name, o.Id
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerId = c.Id AND o.Status = 'Paid';

-- With an index on Name: a leading wildcard cannot seek; a prefix can
SELECT Id FROM dbo.Customers WHERE Name LIKE '%son';
SELECT Id FROM dbo.Customers WHERE Name LIKE 'Ander%';

Line-by-line walkthrough

1. 1. The first query wraps CreatedAt in YEAR(). The database must compute the year for each row, so it scans.
2. 2. The second query compares the bare column to a date range. That is sargable, so an index seek is possible.
3. 3. The CREATE INDEX builds a separate structure sorted by CreatedAt, with Total stored in it. The table itself is not re-sorted, and this query never visits it.
4. 4. In the LEFT JOIN, the Status test sits in the ON clause. Customers with no paid orders still appear, with NULL.
5. 5. LIKE with a leading % cannot use the sort order, so it scans. LIKE with a prefix can seek, if Name has an index.

Spot the bug

-- SQL Server (T-SQL)
CREATE INDEX IX_Orders_CreatedAt ON dbo.Orders (CreatedAt) INCLUDE (Total);

-- Orders has 20 million rows, but this still takes 15 seconds
SELECT Id, Total
FROM dbo.Orders
WHERE YEAR(CreatedAt) = 2025;

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• Clustered and nonclustered indexes described (Microsoft Learn)
• Execution plans in SQL Server (Microsoft Learn)
• PostgreSQL: Indexes (PostgreSQL Documentation)