Cross and Self Join

The previous two lessons built a join on an equality condition: the foreign key of the left-hand row equal to the primary key of the right-hand row. The condition does not always have to take this shape — a join with no condition at all is defined too.

This lesson looks at two special cases. The first is the unconditioned join: the product in which every row matches every other row. The second is a table joined with itself: queries comparing two rows drawn from the same table. Neither is an exception to the rule; both are edge cases of the same rule.

The block below builds the schema and the sample data; every query in this lesson runs against the library.db file it creates.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

The Cartesian Product

A cross join takes no condition at all: every row on the left is matched with every row on the right. The result’s row count is the product of the two tables’ row counts. Its counterpart in set theory is the Cartesian product.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS book_rows FROM book;
SELECT COUNT(*) AS branch_rows FROM branch;
SELECT COUNT(*) AS product FROM book CROSS JOIN branch;
SQL

book_rows
---------
7        
branch_rows
-----------
4          
product
-------
28     

Seven times four, twenty-eight. The product looks harmless in a small example; across two tables with ten thousand rows each, it means a hundred million rows. This is the most expensive result a poorly written query can produce, and it is one of the first symptoms looked for when reading a query plan in the Advanced SQL course.

The result can be kept to a visible size by filtering the right-hand side.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT br.name AS branch, b.title
FROM branch AS br
CROSS JOIN book AS b
WHERE b.author = 'Oğuz Atay'
ORDER BY br.branch_id, b.book_id;
SQL

branch        title            
------------  -----------------
Central       The Disconnected 
Central       Tehlikeli Oyunlar
Bahcelievler  The Disconnected 
Bahcelievler  Tehlikeli Oyunlar
Kadikoy       The Disconnected 
Kadikoy       Tehlikeli Oyunlar
Konak         The Disconnected 
Konak         Tehlikeli Oyunlar

Four branches, two books, eight rows. No row is claiming “this book is at this branch” — a cross join does not produce a fact, it produces a list of possibilities. That is also where it becomes useful: building every possible combination of every branch with every book, then overlaying the ones that actually exist with an outer join, gives a complete report grid in which the empty cells are visible too.

The Accidental Product

A cross join is rarely written on purpose; it appears when a join condition is forgotten. In the old comma-separated syntax, where the condition is left to the WHERE clause, this slip is especially easy to make.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS no_condition FROM book, branch;
SQL

no_condition
------------
28          

The result matches the explicitly written cross join exactly: twenty-eight. The query raised no error. The previous lesson’s conditioned join on these same two tables returned six rows.

The symptom is recognizable: far more rows than expected, repeated values, and totals that multiply. The remedy is a matter of syntax too — the JOIN … ON form keeps the condition next to the join, so forgetting it stands out. Writing CROSS JOIN also documents that the product was intentional; a reader of the query can see that the product was the plan.

Self Join

A table can be joined with itself. Doing so requires giving the table two different aliases; from the engine’s point of view, this is exactly like two separate tables. The name for the pattern is self join, and it is not a separate join type — it is an instance of an inner or outer join in which the same table appears on both sides.

The query finding pairs of books by the same author is written first in its unconditioned form.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT a.title AS book_a, b.title AS book_b
FROM book AS a
JOIN book AS b ON a.author = b.author
ORDER BY a.book_id, b.book_id;
SQL

book_a             book_b           
-----------------  -----------------
Blindness          Blindness        
The Disconnected   The Disconnected 
The Disconnected   Tehlikeli Oyunlar
The Book of Sand   The Book of Sand 
Yaban              Yaban            
Silent House       Silent House     
Motherland Hotel   Motherland Hotel 
Tehlikeli Oyunlar  The Disconnected 
Tehlikeli Oyunlar  Tehlikeli Oyunlar

Only two of the nine rows are meaningful, and those two are the same pair seen from both directions. The two typical flaws of a self join show up here. First, every row matches itself — because its author equals itself. Second, the real pair appears twice, with its direction reversed.

Both are removed by a single condition: tying the match to the order of the primary key.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT a.author, a.title AS first, b.title AS second
FROM book AS a
JOIN book AS b ON a.author = b.author AND a.book_id < b.book_id
ORDER BY a.book_id;
SQL

author     first             second           
---------  ----------------  -----------------
Oğuz Atay  The Disconnected  Tehlikeli Oyunlar

The condition a.book_id < b.book_id does two jobs at once. Because equality is excluded, a row can no longer match itself; because the ordering runs one way, each pair appears only once. Writing the strict inequality as <> instead would solve only the first problem, leaving the mirrored duplicates in place.

The same pattern applies to another question: pairs of branches in the same city.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT a.city, a.name AS branch_1, b.name AS branch_2
FROM branch AS a
JOIN branch AS b ON a.city = b.city AND a.branch_id < b.branch_id;
SQL

city    branch_1  branch_2    
------  --------  ------------
Ankara  Central   Bahcelievler

Chaining a Self Join

A self join can be combined with other joins. Finding pairs of members who borrowed the same book requires joining the loan table with itself, and then joining that result with the member and book tables.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT b.title, m1.first_name AS member_1, m2.first_name AS member_2
FROM loan AS l1
JOIN loan AS l2 ON l1.book_id = l2.book_id AND l1.member_id < l2.member_id
JOIN member AS m1  ON m1.member_id = l1.member_id
JOIN member AS m2  ON m2.member_id = l2.member_id
JOIN book AS b   ON b.book_id = l1.book_id
ORDER BY b.book_id, m1.member_id, m2.member_id;
SQL

title             member_1  member_2
----------------  --------  --------
Blindness         Alice     Ben     
Blindness         Alice     Derek   
Blindness         Ben       Derek   
The Disconnected  Alice     Grace   
The Book of Sand  Clara     Grace   
Yaban             Clara     Derek   

Five table references appear, but the table count is three: the loan table appears twice, the member table appears twice. Aliases are not optional here — nothing else could say which member_id column is meant.

Blindness produced three rows because three separate members borrowed it: the number of pairs chosen from three items is three. A self join’s row count grows with the square of the group size; in large groups this can become as expensive as a cross join.

Summary

• A cross join is unconditioned; its row count is the product of the two tables’ row counts.
• The product is not a list of facts but a list of possibilities; it is useful for building a report grid.
• An accidental cross join forms without error when a join condition is forgotten; its symptom is far more rows than expected and multiplying totals.
• A self join is not a separate type; it is referencing the same table under two aliases, and the alias is required.
• An unconditioned self join matches every row with itself and every pair in both directions; a strict inequality on the primary key removes both at once.
• The cost of a pair-producing join grows with the square of the group size.

Next Step

Every query so far has stayed at row level: each result row corresponded to some combination of source rows. Questions like “how many books,” “average loan duration,” and “earliest publication year” summarize not rows but sets of rows. The next lesson builds the aggregate functions that reduce a row set to a single value, and measures why those functions do not count null values.