--- title: 'Cross and Self Join' source: 'https://academia.sh/en/courses/sql-fundamentals/cross-and-self-join' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # Cross and Self Join The Cartesian product an unconditioned join produces, the signs of an accidental product, connecting a table to itself under two aliases, and eliminating duplicate pairs. 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. ```sh 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','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','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**. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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.