--- title: 'Outer Joins' source: 'https://academia.sh/en/courses/sql-fundamentals/outer-joins' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # Outer Joins Preserving rows that have no match, the way preserved rows fill with null values, the pattern that finds the unmatched, engine support for right and full outer joins, and how the position of a condition changes the result. The previous lesson showed a limit of the inner join: a row that fails the condition never appears in the result at all. Once books were joined with branches, the book with no assigned branch dropped out, and the result came back six rows instead of seven. Some questions ask for exactly those dropped rows. "Which branch has no books at all," "which member has never borrowed anything," "which book has never been picked up by anyone" — all three ask for rows that have no match. An inner join cannot answer them, because it eliminates the very thing being asked about. This lesson builds the join type that keeps unmatched rows. 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 ``` ## Left Outer Join A **left outer join** keeps every row of the left-hand table. For rows in it that have no match in the right-hand table, the columns coming from the right table are filled with a null value. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT b.title, br.name AS branch FROM book AS b LEFT JOIN branch AS br ON b.branch_id = br.branch_id ORDER BY b.book_id; SQL ``` ``` title branch ----------------- ------------ Blindness Central The Disconnected Central The Book of Sand Bahcelievler Yaban Bahcelievler Silent House Kadikoy Motherland Hotel Kadikoy Tehlikeli Oyunlar ``` All seven books came back. The branch name in the `Tehlikeli Oyunlar` row is empty: the book was kept, but no match means the column coming from the right table could not be filled. The word `OUTER` is optional; `LEFT JOIN` and `LEFT OUTER JOIN` mean the same thing. The source of the null value matters here. The branch name column is declared `NOT NULL`, so no branch row has an empty name. The gap in the result does not come from the data; **the join itself produced it**. A null value in an outer join's result can mean one of two different things: either the source row genuinely held a null value, or there was no match at all. When the distinction matters, it is settled by testing the right table's primary key. Reversing the direction also reverses which side is preserved. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, b.title FROM branch AS br LEFT JOIN book AS b ON b.branch_id = br.branch_id ORDER BY br.branch_id, b.book_id; SQL ``` ``` branch title ------------ ---------------- Central Blindness Central The Disconnected Bahcelievler The Book of Sand Bahcelievler Yaban Kadikoy Silent House Kadikoy Motherland Hotel Konak ``` Now every branch is visible; `Konak`, which has no books, is on the list too, with an empty title column. Same two tables, same condition, different side preserved. ## Finding the Unmatched The most common pattern with an outer join is picking out the preserved-but-unmatched rows. Since a missing match leaves every column of the right table null, testing the right table's primary key for null selects exactly those rows. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS bookless_branch FROM branch AS br LEFT JOIN book AS b ON b.branch_id = br.branch_id WHERE b.book_id IS NULL; SQL ``` ``` bookless_branch --------------- Konak ``` The same pattern applies to members. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name FROM member AS m LEFT JOIN loan AS l ON l.member_id = m.member_id WHERE l.loan_id IS NULL; SQL ``` ``` first_name last_name ---------- --------- Owen Park ``` The test needs to run against the **primary key**. Testing a column that accepts null values instead would give a wrong result: rows that genuinely matched but happened to hold a null in that particular column would slip into the list as well. Because a primary key can never be null, seeing it as null means exactly one thing — no match. ## Right and Full Outer Join The mirror image of a left outer join is the **right outer join**: the rows of the right-hand table are preserved instead. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, b.title FROM book AS b RIGHT JOIN branch AS br ON b.branch_id = br.branch_id ORDER BY br.branch_id, b.book_id; SQL ``` ``` branch title ------------ ---------------- Central Blindness Central The Disconnected Bahcelievler The Book of Sand Bahcelievler Yaban Kadikoy Silent House Kadikoy Motherland Hotel Konak ``` The result is identical to the `branch LEFT JOIN book` query from the earlier section. This is not a coincidence: **every right outer join can be rewritten as a left outer join by reversing the table order.** There is no difference in meaning between the two spellings. This equivalence carries practical weight, because right outer join support **varies by engine** — some engines implement only the left outer join. Writing a query in the left form removes the portability problem before it starts. It is preferred for readability too: the preserved table sits in the `FROM` clause, the first place a reader's eye reaches. A **full outer join** keeps the unmatched rows from both sides at once. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, b.title FROM book AS b FULL OUTER JOIN branch AS br ON b.branch_id = br.branch_id ORDER BY br.branch_id, b.book_id; SQL ``` ``` branch title ------------ ----------------- Tehlikeli Oyunlar Central Blindness Central The Disconnected Bahcelievler The Book of Sand Bahcelievler Yaban Kadikoy Silent House Kadikoy Motherland Hotel Konak ``` Eight rows: six matches, one book with no branch, and one branch with no books. Both directions' gaps were gathered into a single result. Support for the full outer join also **varies by engine**, and it is rarer than support for the right outer join. Where it is missing, the equivalent spelling is the union of a left outer join with its reverse-direction left outer join counterpart; set operations are covered in this topic's last lesson. ## The Position of a Condition Changes the Result The most common mistake in an outer join is writing a condition that belongs to the right table inside `WHERE` instead of `ON`. In an inner join the two spellings give the same result; in an outer join they do not. First with the condition inside the join: ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name, l.loan_id, l.pickup_date FROM member AS m LEFT JOIN loan AS l ON l.member_id = m.member_id AND l.pickup_date >= '2025-05-01' ORDER BY m.member_id, l.loan_id; SQL ``` ``` first_name last_name loan_id pickup_date ---------- --------- ------- ----------- Alice Kane 9 2025-05-14 Ben Ortiz 11 2025-06-11 Clara Diaz Derek Voss 12 2025-06-20 Grace Kim 8 2025-05-02 Grace Kim 10 2025-06-03 Owen Park ``` All six members are on the list. The two members with no loan record meeting the condition were kept, with the loan columns left null. Now the same condition, in the filter clause instead: ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name, l.loan_id, l.pickup_date FROM member AS m LEFT JOIN loan AS l ON l.member_id = m.member_id WHERE l.pickup_date >= '2025-05-01' ORDER BY m.member_id, l.loan_id; SQL ``` ``` first_name last_name loan_id pickup_date ---------- --------- ------- ----------- Alice Kane 9 2025-05-14 Ben Ortiz 11 2025-06-11 Derek Voss 12 2025-06-20 Grace Kim 8 2025-05-02 Grace Kim 10 2025-06-03 ``` Two members disappeared. The reason is the rule from the fifth lesson: the join filled the loan columns of the preserved rows with null values, and the `WHERE` clause then evaluated `NULL >= '2025-05-01'` for those rows, which came out unknown. `WHERE` only lets true through, so the preserved rows were eliminated. The rule can be stated this way: the `ON` clause decides **which rows match**, and the `WHERE` clause decides **which rows remain after the join is done**. A condition that belongs to the right table, when written inside `WHERE`, silently turns an outer join back into an inner join. The one exception to this collapse is a deliberately written null test — the pattern that finds the unmatched relies on exactly that effect. ## Summary - A left outer join keeps every row of the left-hand table and fills the right table's columns with null values where there is no match. - A null value in the result either comes from the source data or was produced by the join itself; the distinction is settled by testing the right table's primary key. - The pattern that finds unmatched rows tests the right table's primary key for null after an outer join. - Every right outer join turns into a left outer join by reversing the table order; support for right and full outer joins varies by engine. - A full outer join keeps the unmatched rows from both sides. - A condition belonging to the right table, when written inside `WHERE`, silently turns an outer join back into an inner join; the condition must be written inside `ON`. ## Next Step Every join covered so far has rested on an equality condition. What happens if the condition is removed entirely, or a table is joined with itself? The next lesson takes up these two special cases: the product an unconditioned join produces, and the comparisons built by connecting a table to itself under two different aliases.