Outer Joins

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.

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

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.

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.

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.

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.

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.

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.

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:

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:

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.