SQL Joins, Set Operations and Subqueries: 10 Code-Along Examples

Learn to combine tables by running the queries. Ten copy-and-run examples covering INNER and LEFT JOIN, silent row loss, the fan-out trap, CROSS and SELF joins, UNION and EXCEPT, subqueries in three positions, and semi against anti joins.

The SQL joins article covers the step where a database stops being a set of tables and starts being a model of something: loans that belong to members, members who belong to branches, books that may never have been borrowed at all. Joins are also where SQL fails most quietly, because the wrong join type does not error, it just returns a number that is too small, and a fan-out does not error either, it returns a total that is too large. Every query here was run against a real database and each grid is what came back, including three results that are wrong in exactly those ways. This workbook works through the whole range: inner joins across three tables, left joins and the rows they rescue, counting the difference, the duplication trap, cross and self joins, the four set operations, subqueries in WHERE, SELECT and FROM, and semi against anti joins. The idea that ties it together arrives in the last example.

The examples run on SQLite and use three tables. Paste the books table from the SQL basics workbook, then these two:

CREATE TABLE loans (
    loan_id INTEGER PRIMARY KEY, book_id INTEGER, member_id INTEGER,
    branch TEXT, loan_date TEXT, days_out INTEGER
);
INSERT INTO loans VALUES
(1,1,101,'Central','2026-01-04',14),(2,2,102,'Central','2026-01-06',9),
(3,2,103,'Riverside','2026-01-11',21),(4,4,101,'Central','2026-01-15',7),
(5,5,104,'Northgate','2026-01-19',12),(6,7,105,'Riverside','2026-02-02',18),
(7,7,102,'Central','2026-02-08',6),(8,9,106,'Northgate','2026-02-12',25),
(9,12,103,'Riverside','2026-02-14',11),(10,12,107,'Central','2026-02-20',8),
(11,12,101,'Central','2026-03-01',16),(12,3,108,'Northgate','2026-03-05',13),
(13,14,105,'Riverside','2026-03-09',5),(14,6,109,'Central','2026-03-14',19),
(15,11,102,'Central','2026-03-18',10),(16,1,110,'Northgate','2026-03-22',22),
(17,9,103,'Riverside','2026-03-27',4),(18,7,111,'Central','2026-04-02',15),
(19,4,104,'Northgate','2026-04-07',17),(20,2,112,'Riverside','2026-04-11',3);
CREATE TABLE members (
    member_id INTEGER PRIMARY KEY, name TEXT, branch TEXT, joined TEXT, tier TEXT
);
INSERT INTO members VALUES
(101,'R. Whitfield','Central','2021-06-12','gold'),
(102,'S. Adeyemi','Central','2022-02-03','gold'),
(103,'M. Kovac','Riverside','2020-11-27','silver'),
(104,'J. Lindqvist','Northgate','2023-05-14','silver'),
(105,'A. Bhatt','Riverside','2019-09-08','gold'),
(106,'D. Moreau','Northgate','2024-01-22','bronze'),
(107,'L. Castillo','Central','2022-08-30','bronze'),
(108,'F. Nakamura','Northgate','2021-03-17','silver'),
(109,'C. Owusu','Central','2025-02-11','bronze'),
(110,'E. Sorensen','Northgate','2018-07-04','gold'),
(113,'V. Petrova','Riverside','2026-01-09','bronze');

1. INNER JOIN across three tables

An inner join keeps rows that match on both sides, and chaining them lets you assemble a readable record from three tables.

SELECT l.loan_id, b.title, m.name
FROM loans AS l
INNER JOIN books   AS b ON l.book_id   = b.book_id
INNER JOIN members AS m ON l.member_id = m.member_id
ORDER BY l.loan_id
LIMIT 6;

loan_id  title           name
-------  --------------  ------------
1        The Long Cold   R. Whitfield
2        Salt and Iron   S. Adeyemi
3        Salt and Iron   M. Kovac
4        Small Machines  R. Whitfield
5        The Quiet Grid  J. Lindqvist
6        The Understudy  A. Bhatt
(6 rows)

Three tables, two ON clauses, one readable result. The table aliases are doing more work than they look: they shorten every column reference, and once two tables both have a book_id they are the only thing that makes the query unambiguous. Note the shape, since the second join is written against the result of the first, which is why loans is the natural table to start from here: it is the one that references the other two. The word INNER is optional in every major engine, so a bare JOIN means the same thing, and writing it out is worth the five characters because the next example is a different join and you want the difference visible.

2. LEFT JOIN and the rows it rescues

A left join keeps every row from the left table whether or not it found a match, filling the gaps with NULL.

SELECT m.member_id, m.name, COUNT(l.loan_id) AS loans
FROM members AS m
LEFT JOIN loans AS l ON m.member_id = l.member_id
GROUP BY m.member_id, m.name
ORDER BY loans, m.member_id
LIMIT 6;

member_id  name         loans
---------  -----------  -----
113        V. Petrova   0
106        D. Moreau    1
107        L. Castillo  1
108        F. Nakamura  1
109        C. Owusu     1
110        E. Sorensen  1
(6 rows)

SELECT m.member_id, m.name
FROM members AS m
LEFT JOIN loans AS l ON m.member_id = l.member_id
WHERE l.loan_id IS NULL;

member_id  name
---------  ----------
113        V. Petrova
(1 row)

V. Petrova has never borrowed anything, and an inner join would have deleted her from the analysis entirely. That is the case for left joins: the question “who has not done this” is only answerable from a result that contains the non-doers. Two details make the first query work. COUNT(l.loan_id) counts a column from the right table and therefore returns zero for unmatched rows, where COUNT(*) would have counted the NULL-filled row and returned one, which is a common and invisible off-by-one. And the second query shows the anti-join pattern, testing the right table’s key for NULL after a left join, which is the standard way to ask for “rows with no counterpart”.

3. Counting what the join type costs you

The difference between join types is a number, and it is worth measuring rather than assuming.

SELECT COUNT(*) AS loans_total FROM loans;
SELECT COUNT(*) AS inner_rows  FROM loans l INNER JOIN members m ON l.member_id = m.member_id;
SELECT COUNT(*) AS left_rows   FROM loans l LEFT  JOIN members m ON l.member_id = m.member_id;

loans_total    inner_rows    left_rows
-----------    ----------    ---------
20             18            20

Two loans disappeared. Members 111 and 112 appear in loans but not in members, so the inner join dropped those rows and nothing said a word about it, while the left join kept all twenty and left the member columns NULL. Ten percent of the data vanished from a query that looks completely reasonable, and if the next line had been SUM(days_out) you would have reported a total that was quietly wrong. Running this three-way count is a habit worth having whenever a join goes into anything important: if the inner join returns fewer rows than the driving table, you have referential gaps and you need to decide what they mean rather than letting the join decide for you.

4. The fan-out trap

The other direction of join damage is duplication, and it does not lose rows, it invents them.

SELECT COUNT(*)      AS rows_after_join,
       SUM(b.copies) AS copies_sum
FROM books AS b
INNER JOIN loans AS l ON b.book_id = l.book_id;

rows_after_join  copies_sum
---------------  ----------
20               196
(1 row)

SELECT COUNT(*) AS books, SUM(copies) AS true_copies FROM books;

books  true_copies
-----  -----------
15     104
(1 row)

The library owns 104 copies and the joined query says 196. Nothing is broken: joining a one-row-per-book table to a many-rows-per-book table repeats each book once per loan, so a book with fifteen copies borrowed three times contributes forty-five to the sum. This is the single most common way real reporting goes wrong, because the query runs, the number is plausible, and the error scales with how popular each item is. Two defences. Aggregate the many-side first in a subquery and join the result, so each book stays one row. Or check the row count immediately after every join and be suspicious the moment it exceeds the row count of the table you meant to describe.

5. CROSS JOIN, every combination

A cross join pairs every row on the left with every row on the right, which is a bug when accidental and useful when deliberate.

SELECT b.branch, t.tier
FROM (SELECT DISTINCT branch FROM members) AS b
CROSS JOIN (SELECT DISTINCT tier FROM members) AS t
ORDER BY b.branch, t.tier;

branch     tier
---------  ------
Central    bronze
Central    gold
Central    silver
Northgate  bronze
Northgate  gold
Northgate  silver
Riverside  bronze
Riverside  gold
Riverside  silver
(9 rows)

Three branches times three tiers gives nine rows, and none of those pairs came from the data, they came from the multiplication. That is exactly what a cross join is for: building the complete grid of combinations that should exist so you can left-join your actual data onto it and see which combinations have nothing. Without that scaffold, a report of loans per branch and tier silently omits the combinations with zero rows, and a zero is usually the most interesting number on the page. The danger is the accidental version, since a join written with a missing or wrong ON clause degenerates into this and a thousand rows meeting a thousand rows becomes a million.

6. SELF JOIN, a table against itself

Joining a table to itself compares rows within one table, which is how you find pairs.

SELECT a.title AS book_a, b.title AS book_b, a.genre
FROM books AS a
INNER JOIN books AS b
  ON a.genre = b.genre AND a.book_id < b.book_id
WHERE a.author = b.author
ORDER BY a.genre;

book_a            book_b           genre
----------------  ---------------  ---------------
The Bone Orchard  Static           Horror
Harbour Lights    The Understudy   Literary
Harbour Lights    Weathering       Literary
The Understudy    Weathering       Literary
Small Machines    The Quiet Grid   Science Fiction
The Long Cold     Salt and Iron    Thriller
Thriller pairs continue
(7 rows)

Pairs of books by the same author in the same genre, from one table joined to itself under two aliases. Two things make this work and both are worth stealing. The aliases are mandatory rather than stylistic, because without them every column reference is ambiguous. And a.book_id < b.book_id in the join condition is the trick that stops a row matching itself and stops each pair appearing twice in both orders, which would otherwise turn seven useful rows into twenty-one mostly useless ones. The same shape answers “employees and their managers”, “consecutive events for the same user”, and any other question about relationships inside a single table.

7. Set operations

Four operators stack result sets vertically rather than joining them horizontally.

SELECT branch FROM members
UNION
SELECT branch FROM loans;

branch
---------
Central
Northgate
Riverside
(3 rows)

SELECT member_id FROM members
EXCEPT
SELECT member_id FROM loans;

member_id
---------
113
(1 row)

UNION stacks two results and removes duplicates, which is why 31 rows of branch names collapse to three. UNION ALLkeeps every row and is both faster and usually what you actually want when you are appending, say, this month’s data to last month’s, because deduplicating a million rows to remove nothing is pure cost. INTERSECT keeps rows present in both results and EXCEPT keeps rows in the first that are not in the second, which is the set-based way to write the anti-join from Example 2 and returns the same member. All four require the two queries to have the same number of columns in compatible types, and they match by position, not by name, so a mismatched column order will run and produce nonsense.

8. Subqueries in WHERE and SELECT

A subquery is a query used as a value, and where you put it decides what it does.

SELECT title, rating
FROM books
WHERE rating > (SELECT AVG(rating) FROM books)
ORDER BY rating DESC;

title           rating
--------------  ------
Static          4.8
Small Machines  4.7
The Understudy  4.6
Salt and Iron   4.5
Harbour Lights  4.4
Second Sun      4.4
Cold Open       4.3
(7 rows)

SELECT title, genre, rating,
       (SELECT ROUND(AVG(rating), 2) FROM books AS b2 WHERE b2.genre = b1.genre) AS genre_avg
FROM books AS b1
WHERE genre = 'Thriller'
ORDER BY rating DESC;

title            genre     rating  genre_avg
---------------  --------  ------  ---------
Salt and Iron    Thriller  4.5     4.02
Cold Open        Thriller  4.3     4.02
The Long Cold    Thriller  4.2     4.02
Follow the Wire  Thriller  3.6     4.02
The Inventory    Thriller  3.5     4.02
(5 rows)

The first subquery runs once and produces a single number that the filter compares against, which is the answer to “above average” questions and cannot be done in one pass without it, since you cannot write WHERE rating > AVG(rating). The second is correlated: it references b1 from the outer query, so it runs once per row and returns that row’s own genre average, which is how you put a group-level figure beside every record for comparison. Correlated subqueries are the expensive kind, conceptually a loop, and on a large table the same result is usually better expressed as a window function or a join to a grouped subquery.

9. Subqueries in FROM, and semi against anti joins

A subquery in FROM is a temporary table, which is how you aggregate twice.

SELECT genre, ROUND(AVG(titles), 1) AS avg_titles_per_author
FROM (
    SELECT genre, author, COUNT(*) AS titles
    FROM books
    GROUP BY genre, author
) AS per_author
GROUP BY genre
ORDER BY avg_titles_per_author DESC;

genre            avg_titles_per_author
---------------  ---------------------
Literary         2.0
Thriller         1.7
Science Fiction  1.5
Horror           1.5
(4 rows)

SELECT title FROM books
WHERE book_id NOT IN (SELECT book_id FROM loans)
ORDER BY title;

title
---------------
Fieldwork
Follow the Wire
The Inventory
Weathering
(4 rows)

The first query aggregates an aggregate, counting titles per author and then averaging those counts per genre, which no single GROUP BY can express. The inner query becomes a named table and the outer one treats it as though it had always existed; naming it is mandatory in most engines. The second query is an anti join written with NOT IN, listing the four books nobody has borrowed, and IN gives you the semi join, the eleven that someone has. Both are the right shape when you want rows from one table filtered by presence in another without adding the other table’s columns, which is what stops them fanning out the way Example 4 did. One warning that costs people hours: if the subquery inside NOT IN can return a NULL, the whole condition returns no rows at all, so use NOT EXISTS when the column is nullable.

10. Putting it together, and the model

A single query using a join, an aggregate, a group and a group filter, which is what most real analytical SQL looks like.

SELECT b.title,
       COUNT(l.loan_id)        AS times_out,
       ROUND(AVG(l.days_out), 1) AS avg_days
FROM books AS b
INNER JOIN loans AS l ON b.book_id = l.book_id
GROUP BY b.book_id, b.title
HAVING COUNT(l.loan_id) > 1
ORDER BY times_out DESC, b.title;

title           times_out  avg_days
--------------  ---------  --------
Salt and Iron   3          11.0
Static          3          11.7
The Understudy  3          13.0
Cold Open       2          14.5
Small Machines  2          12.0
The Long Cold   2          18.0
(6 rows)

Note the GROUP BY b.book_id, b.title rather than title alone, which is the habit that survives two books sharing a title.

The model that ties the ten together is that every way of combining tables answers one of three questions, and naming the question tells you which tool to use. The first is “put these side by side”, which is a join, and the only decisions are which rows survive a failed match, which is the join type, and whether the right-hand table can match more than once, which is the fan-out risk. The second is “stack these on top of each other”, which is a set operation, where the columns must line up by position and the only real choice is whether duplicates matter, which is UNION against UNION ALL. The third is “use this result as a value”, which is a subquery, and the position decides its role: in WHERE it is a filter, in SELECT it is an extra column computed per row, and in FROM it is a table you can aggregate again. Almost every wrong answer in this workbook came from answering the first question with the wrong join type or ignoring the fan-out, which is why the row count before and after a join is the cheapest test in SQL and the one most worth running.

Work through these and you have the article in practice: an inner join across three tables and the aliases that keep it unambiguous; a left join that rescues the member who never borrowed anything, and the COUNT that must name a column to return zero; two loans silently deleted by the wrong join type; a copy count inflated from 104 to 196 by a fan-out; a cross join used deliberately to build a grid; a self join with the inequality that stops duplicate pairs; the four set operations and the positional column matching they rely on; subqueries in WHERE and correlated in SELECT; a subquery in FROMaggregating an aggregate, plus semi and anti joins with the NOT IN NULL trap; and a complete analytical query. The habit that follows is a numeric one: count the rows before the join and after it, every time, because a join that changes the row count in either direction is telling you something about your data that the result grid will not.

Thanks for reading, Andrei.