In the previous lesson we learned to interrogate one table. But the questions BiblioRed really cares about do not fit inside a single table: "who has copy EJ-3081 right now?", "which members have never borrowed anything?", "which author wrote the book that comes back late most often?". The answers are spread across members, loans, copies, books and authors.

That spread is not a defect: it is exactly what we decided in lesson 02-01 when we separated the work (books) from the physical object (copies), and when we chose not to repeat the member's name in every loan. Information is stored once, in the place where it belongs, and reassembled when needed. The tool that reassembles it is the JOIN, relational algebra's join ⋈.

This is the lesson where SQL stops looking like a table browser and starts looking like a real query language. It is long and dense; take it slowly and run every example against your biblioredb.

1. Why the data has to be reassembled
2. Cartesian product and CROSS JOIN
3. INNER JOIN: the basic join
4. LEFT JOIN and negative questions
5. RIGHT JOIN and FULL OUTER JOIN
6. Visual summary of the JOIN types
7. SELF JOIN: joining a table to itself
8. Chaining three or more tables
9. Filtering in ON or filtering in WHERE
10. Scalar subqueries
11. Subqueries with IN, EXISTS and NOT EXISTS
12. Correlated subqueries
13. Derived tables: subqueries in FROM
14. Common table expressions: WITH
15. Set operators: UNION, INTERSECT, EXCEPT
16. Common mistakes and tips
17. Exercises
18. Conclusion

Look at the loans table raw:

To a human being this says nothing: 14, 2, 15, 1… are references. The member's name is in members, the copy's code in copies and the title in books. The alternative —storing the name and the title inside each loan— is precisely what the spreadsheet did, and we already saw the result: redundancy, inconsistency and typing errors.

The relational model's bargain is this: you store without repeating, and you pay for a JOIN when you query. It is an excellent bargain, because writing well happens once and reading badly happens forever.

Before pairing rows correctly, you have to understand what happens if you do not pair them. The Cartesian product × combines every row of one table with every row of the other.

First rows:

4 branches × 8 authors = 32 rows. With members (10) and books (9) it would be 90; with BiblioRed's real tables, 12,000 × 8,000 = 96 million.

Is it good for anything? Yes, in one specific case: generating every possible combination of two sets, for example to build a "each branch × each possible status" grid that is then filled in with data. Outside of that, a Cartesian product in production is almost always an accident.

Before SQL-92 the word JOIN did not exist: tables were listed in the FROM separated by commas and the matching condition was written in the WHERE.

The danger is obvious: if you forget the WHERE condition, you get a Cartesian product in silence. Twelve loans times ten members are 120 rows that look like legitimate data. With large tables, the query hangs.

With the modern syntax, JOIN ... ON, the condition is glued to the join and cannot be lost from sight. Always use an explicit JOIN.

INNER JOIN is a Cartesian product followed by a filter: it returns only the pairs of rows that satisfy the condition. The word INNER is optional (a bare JOIN means INNER JOIN), but writing it makes clear that it is not a LEFT.

Twelve rows: one per loan. Marta Alsina appears three times because she has three loans; that is not duplication, it is reality.

l and m are table aliases. They are not mandatory, but they are highly advisable:

• They shorten references: m.last_name instead of members.last_name.
• They are indispensable when two tables have columns with the same name. If you write SELECT member_id FROM loans JOIN members ON ..., the manager does not know which of the two you mean:

• They are mandatory in a SELF JOIN (section 7).

Style advice: use recognizable initials (m members, l loans, c copies, b books, a authors, br branches) and be consistent across the whole project. In this course we will always use those same ones.

The four members with something outstanding, with each one's deadline.

When the columns in both tables are called exactly the same, there is a shorthand:

USING (member_id) is equivalent to ON m.member_id = l.member_id, and it also merges the two columns into one in the result. It is convenient and our schema allows it, because we named the foreign keys the same as the primary ones.

There is also NATURAL JOIN, which automatically matches on all columns whose names coincide:

Here copies and books share book_id… but if one day somebody adds a notes column to both, the NATURAL JOIN will start matching on that too and the query will change meaning without anyone having touched it. It is implicit magic: avoid it. Explicit ON or, at most, USING.

INNER JOIN throws away whatever does not match. Sometimes that is precisely what you do not want.

It returns 12 rows and only 8 members appear: Ramón Etxebarri (13) and Elena Roig (20) have vanished.

LEFT JOIN (short for LEFT OUTER JOIN) keeps all the rows of the left-hand table; when there is no partner on the right, it fills those columns with NULL.

14 rows: the 12 loans plus the two "empty" rows for Etxebarri and Roig.

And here comes one of the most useful patterns in all of SQL. If the unmatched rows are the ones with NULL in the right-hand columns, it is enough to filter for them:

The two members who have never borrowed anything. It is relational algebra's difference −, implemented with a LEFT JOIN.

A crucial detail: the column you check with IS NULL must be one that can never be null in the right-hand table —the primary key is the safe choice—. If you checked WHERE l.return_date IS NULL you would get something completely different (the open loans, plus the members with no loans).

Another real BiblioRed case: the copies that have never been lent, candidates for weeding.

Six copies that have sat on the shelf without ever going out. Exactly the kind of report the spreadsheet could not produce.

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps all the rows of the right-hand table.

Nine rows: the eight authors, with Óscar Barreda repeated for his two works and Marina Escolá showing NULL because she has none yet. Notice that "Ensanche Records (1904)" does not appear: it is the book with no cataloged author, and RIGHT JOIN keeps the authors, not the books.

Every RIGHT JOIN query can be rewritten as a LEFT JOIN by swapping the tables, and that is the form you will see in 95% of professional code:

Advice: stick with LEFT JOIN. Reading a long query in which some JOINs go left and others go right is needlessly hard.

It keeps the unmatched rows from both sides:

Ten rows: both the author with no books and the book with no author show up. It is the audit query par excellence: it shows the two loose ends at once.

RIGHT JOIN and FULL OUTER JOIN did not exist in SQLite until version 3.39 (June 2022). If your sqlite3 is older, those two queries will raise a syntax error. Check your version with SELECT sqlite_version();. Workarounds:

• Rewrite the RIGHT JOIN as a LEFT JOIN with the tables swapped (always possible).
• Simulate the FULL OUTER JOIN with two LEFT JOINs combined with UNION (section 15).

LEFT JOIN and INNER JOIN, by contrast, have worked in SQLite forever.

Imagine two minimal tables matched on a key:

• LEFT with keys 1, 2, 3
• RIGHT with keys 2, 3, 4

And the decision tree worth keeping in your head when writing a query:

It is not a different kind of JOIN: it is an ordinary INNER or LEFT JOIN in which both tables are the same one. It is used to compare rows of a table with each other, and that is why aliases are mandatory: you have to be able to tell the two "copies" apart.

Question: which pairs of members are signed up at the same branch?

Ten pairs. The condition m2.member_id > m1.member_id does two things at once and is the trick to memorize:

1. It stops each member from being paired with themselves (which would be m1.member_id = m2.member_id).
2. It avoids the symmetric duplicate: if (Ferrán, Roig) comes out, (Roig, Ferrán) does not come out as well.

Without that condition you would get 4×4 + 3×3 + 2×2 + 1×1 = 30 rows instead of 10.

Another SELF JOIN that is useful in BiblioRed: other works by the same author.

Here we do want both directions (so we can recommend starting from either of the two books), which is why we use <> instead of >. Óscar Barreda is the only author with two works in the collection.

The canonical real-world use of the SELF JOIN is hierarchies: an employees table with a manager_id column that points back at the same table. BiblioRed has none, but the mechanism is identical.

JOINs are chained top to bottom: the result of the first is joined with the next table, and so on. BiblioRed's star query walks through five tables.

This is the listing the librarian wants to see every morning. And we can now answer the question we opened with: the person who has EJ-3081 right now is Marta Alsina, and she has to return it on 4 August 2026.

Three decisions in that query deserve comment:

• We start from loans, not from members. When you chain several tables, starting at the "central" table —the one holding the foreign keys to the rest— makes the query far more natural to read.
• authors is joined with a LEFT JOIN. Why? Because books.author_id accepts NULL (remember "Ensanche Records"). With an INNER JOIN, if somebody lent that copy, the loan would disappear from the morning report without anyone noticing. It is the most frequent silent error when chaining tables: an INNER JOIN on an optional foreign key loses rows.
• The order in which you write the JOINs does not determine the execution order. The optimizer (lesson 01-04) decides on its own. You write for readability; it executes for speed.

This distinction is subtle, it comes up in every interview and it produces incorrect results on a daily basis. With INNER JOIN it makes absolutely no difference where you put the condition. With LEFT JOIN, it changes the result completely.

The reason lies in the evaluation order:

1. ON is applied while the rows are being matched: it decides what is a match and what is not.
2. WHERE is applied after the join has been built: it discards rows from the assembled result, including the NULL-filled rows that the LEFT JOIN had just preserved.

Question: "list of all members, showing which loans they have made from July 2026 onwards".

Ten rows: every member is there, which is what the question asked for. The ones with no July loans come out with NULL.

Four rows. The WHERE has removed every row with l.loan_date at NULL, because NULL >= '2026-07-01' is UNKNOWN. The LEFT JOIN has effectively turned into an INNER JOIN.

Practical rule: in a LEFT JOIN, conditions on the right-hand table go in the ON; conditions on the left-hand table go in the WHERE. The only exception is the anti-join pattern WHERE right_key IS NULL, where you deliberately want that effect.

A subquery is a SELECT inside another SELECT, in parentheses. The simplest variety is the scalar one: it returns exactly one row and one column, that is, a single value, and can therefore be used anywhere a value fits.

The subquery runs once, returns 2008, and the outer query uses it as if you had typed it by hand. The advantage is that you do not have to know the value in advance: if that book's year is corrected tomorrow, the query is still correct.

If a scalar subquery returns more than one row, the manager raises an error:

If it returns zero rows, by contrast, there is no error: the result is NULL, the whole comparison becomes UNKNOWN, and the outer query returns zero rows… silently. Another application of three-valued logic.

This is equivalent to a LEFT JOIN with books. As a general rule, prefer the JOIN: it is more readable, more flexible (you can bring several columns from the joined table) and the optimizer usually handles it better. A subquery in the SELECT is reserved for when you need a single computed value and the JOIN would complicate the query.

When the subquery returns several rows, you cannot compare it with =, but you can ask about membership or about existence.

Compare it with the JOIN version. With a JOIN you would have to add DISTINCT so as not to repeat the members with several open loans; with IN you do not, because membership of a set is yes or no. That is its main advantage.

It works because reservations.book_id is NOT NULL. Now the same idea on a column that does accept nulls:

Zero rows, when the correct answer is "Marina Escolá". We saw why in 02-01: the subquery returns {1, 2, 3, 4, 5, 6, 7, NULL} (the NULL is the one from "Ensanche Records"), and 8 NOT IN (..., NULL) translates into 8 <> 1 AND ... AND 8 <> NULL, whose last term is UNKNOWN. And TRUE AND UNKNOWN is UNKNOWN, which does not pass the filter. With a single NULL in the list, NOT IN never returns a single row.

The three solutions, from worst to best:

All three now return:

EXISTS does not compare values: it asks whether the subquery returns at least one row. It returns TRUE or FALSE, never UNKNOWN, and that is why it is immune to the previous trap.

The SELECT 1 is a convention: since all that matters is whether there are rows, not which rows, a constant is used. SELECT * would work just the same and the manager optimizes it identically.

Same result as the anti-join in section 4. Three ways of expressing the same question:

The EXISTS subqueries you have just seen have a peculiarity: they mention a column from the outer query (m.member_id). That makes them correlated: they cannot be run on their own, because they depend on the row being evaluated at each moment.

A BiblioRed example that answers a question that would be genuinely hard any other way: which copies belong to a book that has an active reservation? (they are the ones to set aside as soon as they come back to the front desk).

Five copies on alert: the three of "The Map of Time" (two active reservations) and the two of "The Pillars of the Earth" (one).

About performance: the description "it is evaluated once per outer row" is the mental model, not necessarily what happens. Modern optimizers usually transform a correlated subquery into a JOIN internally. Even so, on large tables it is worth measuring; in lesson 06-03 you will learn to check it with EXPLAIN.

A subquery can also take the place of a table in the FROM. It is called a derived table and, like any table, it needs an alias.

The four open loans, split across the branches where each copy lives. The derived table ol (for "open loans") exists only for the duration of the query.

Derived tables solve cases where you need to work on an intermediate result, above all when that intermediate includes aggregates —something we will see in lesson 02-05—. Their drawback is readability: if you nest two or three, the query becomes a labyrinth of parentheses that has to be read from the inside out.

A CTE (common table expression) is a derived table that is given a name before it is used. Same power, incomparably better readability.

It returns exactly the same as the previous section, but now the query reads from top to bottom, like a procedure: "first I work out the open ones, then I cross them with the branches".

They are separated by commas, and each one can use the previous ones:

Three of the four open loans belong to members of the North branch; the fourth is Diego Salom's, who signed up at South.

Advantages of CTEs:

• Readability: each block has a name and a purpose.
• Reuse: a CTE can be referenced several times in the main query, whereas a derived table would have to be written twice.
• Debugging: you can run the CTE's content separately to see what it produces.

Dialect notes:

• WITH is standard and works in PostgreSQL (since 8.4) and SQLite (since 3.8.3).
• There is WITH RECURSIVE for walking hierarchies and graphs (category trees, bills of materials). It is powerful and falls outside the scope of this introductory course.
• Historically PostgreSQL treated every CTE as an optimization fence; since version 12 it folds them into the main query unless you write MATERIALIZED. If you work with an earlier version and notice slowness, that may be the cause.

JOINs combine tables sideways (they add columns). Set operators combine them vertically (they stack rows). They are relational algebra's union ∪, intersection ∩ and difference −.

To use them, the two queries must be compatible: same number of columns and compatible types, in the same order. The column names come from the first query.

Six members. The first query returns {14, 15, 17, 19} and the second {16, 18, 15}; Iván Pereda (15) is in both and appears only once, because UNION removes duplicates.

Seven rows: UNION ALL does not remove duplicates. And precisely for that reason it is faster: it does not have to sort or compare anything. Practical rule: use UNION ALL unless you need the deduplication. Plenty of people write UNION out of habit and pay the cost for nothing.

Iván Pereda: he has "The Pillars of the Earth" on loan and "The Map of Time" reserved.

EXCEPT is not symmetric: A EXCEPT B is not the same as B EXCEPT A. If you swapped the order you would get {16, 18}, the members with an active reservation and no open loans.

• INTERSECT and EXCEPT also remove duplicates by default; INTERSECT ALL and EXCEPT ALL exist.
• The ORDER BY goes at the end, once only, and sorts the combined result. You cannot put one in each branch (except in parentheses with LIMIT).
• In Oracle, EXCEPT is called MINUS.
• SQLite has supported UNION, UNION ALL, INTERSECT and EXCEPT forever; it is its strong point compared with outer joins.

A very practical use in old SQLite: simulating a FULL OUTER JOIN.

The ten rows from section 5, without needing FULL OUTER JOIN.

• Forgetting the join condition. With the old comma syntax it produces a silent Cartesian product. Always use JOIN ... ON.
• Using INNER JOIN on a foreign key that accepts NULL. You lose rows with no warning. If the column can be null, LEFT JOIN.
• Putting the right-hand table's condition in the WHERE of a LEFT JOIN. It turns it into an INNER JOIN and you lose the rows you wanted to keep. It goes in the ON.
• Checking IS NULL on the wrong column in an anti-join. Always use the right-hand table's primary key, which can never be null on its own.
• NOT IN with a subquery that can return NULL. Zero rows, always, silently. Use NOT EXISTS.
• Mistaking "more rows than expected" for a JOIN bug. If a member has three loans, they will appear three times: that is correct. The problem arises when counting (COUNT) over that result, and we will see it in lesson 02-05.
• Chaining JOINs without aliases. With five tables and columns sharing names, a query with no aliases is unreadable and even ambiguous to the manager.
• Using NATURAL JOIN. It matches on coinciding names and changes meaning when somebody adds a column.
• Nesting derived tables three levels deep. Turn them into CTEs with WITH: same result, half the time to understand it.
• Tip: when a multi-table query returns something odd, strip clauses away until it works. Run the bare JOIN first with SELECT *, see how many rows come out and add conditions one at a time.
• Tip: always write the join condition in the order ON new_table.column = table_already_present.column. It is a minor convention, but when reading a five-table query it is enormously appreciated.

1. List the copies with the title of the book they belong to and the name of their branch. Sort by branch and code.
2. Show the active reservations with the member's name and the title of the reserved book.
3. List all the books with their author's last name, including the ones with no cataloged author.
4. Show the loans returned late, giving member, title and both dates.

1. Which books have no copy at the Central branch (branch_id = 1)? Solve it with NOT EXISTS.
2. Which members have never made a single reservation? Solve it two ways: with an anti-join and with NOT EXISTS.
3. Which branches have no copy with status on_loan?
4. Which authors have work in the collection but none of their works has ever been lent?

1. Using a CTE, get the available copies of books that have an active reservation, with their code, title and branch. It is the "set aside for reservations" list.
2. With set operators, get the identifiers of the members who have made a reservation but never a loan.
3. List, for each branch, the members signed up there and the copies it holds… and explain why this should not be done with a single three-table JOIN.

Solution 1

Fifteen rows. The first two and the last two:

With an INNER JOIN you would have lost "Ensanche Records (1904)".

Solution 2

Members 11, 14, 15, 16 and 18 have reserved at some point; the other five, never.

No author satisfies both conditions. Óscar Barreda (4) looked like a candidate, because "Urban Gardening Handbook" has never been lent, but his other work, "Algebra for the Impatient", has (loan 4). It is a good reminder that in a condition like "none of their works" you have to examine all of the author's works, not one at a time.

Solution 3

Three copies the staff must set aside. The DISTINCT in the CTE matters: "The Map of Time" has two active reservations, and without it each of its copies would appear twice.

The five members who have reserved at some point (11, 14, 15, 16, 18) have all made a loan too. Try the inverse operation to see the difference:

Why it must not be done this way: members and copies are not related to each other; both hang off branches independently. Joining them in the same query produces a Cartesian product within each branch: the Central branch has 4 members and 6 copies, so it generates 24 rows. The total is 4×6 + 3×4 + 2×3 + 1×2 = 24 + 12 + 6 + 2 = 44 rows, none of which means anything: they pair Nuria Bastos with a copy she has never touched.

This phenomenon has a name —row explosion or fan trap— and it is one of the most frequent causes of inflated counts. The two correct solutions:

This lesson has turned BiblioRed's seven isolated tables into a queryable system:

• The data is spread on purpose —each fact in one place only— and reassembled at query time with relational algebra's join ⋈.
• The Cartesian product (CROSS JOIN) is the conceptual starting point and the classic accident of the old comma syntax.
• INNER JOIN ... ON returns only what matches; table aliases are all but mandatory and NATURAL JOIN is magic best avoided.
• LEFT JOIN keeps the left-hand table, and its combination with WHERE right_key IS NULL —the anti-join— is the pattern for answering every question that starts with "the ones that never…": members with no loans, copies never lent.
• RIGHT JOIN and FULL OUTER JOIN complete the picture (and arrived late in SQLite, in version 3.39).
• The SELF JOIN compares rows of a table with itself, with the > condition so as not to duplicate symmetric pairs.
• Chaining five tables —member → loan → copy → book → author— answers the front desk's real questions, provided you use LEFT JOIN where the foreign key accepts nulls.
• Filtering in ON is not the same as filtering in WHERE in a LEFT JOIN: the WHERE degrades it to an INNER JOIN.
• Subqueries in their five forms: scalar, with IN, with EXISTS/NOT EXISTS, correlated and as a derived table in the FROM. With one rule burned in: NOT EXISTS instead of NOT IN whenever there may be NULLs.
• CTEs with WITH, which turn a labyrinthine query into a procedure that reads top to bottom.
• The set operators: UNION (deduplicates), UNION ALL (faster), INTERSECT and EXCEPT (which is not symmetric).

Notice that we have repeatedly brushed against a limit: we can list each member's loans, but not count them; we can see each branch's copies, but not how many there are; exercise 3.3 was left half-finished because summarizing two branches required something we do not have yet.

That something arrives in lesson 02-05, Data Aggregation and Grouping: COUNT, SUM, AVG, MIN and MAX, the GROUP BY clause, the difference between HAVING and WHERE, the logical order in which a query is really executed and —very important after what we have just seen— the problem of counting rows inflated by a JOIN. With it, BiblioRed will move from answering "what is there" to answering "how much is there".