Outer joins preserve unmatched rows from one or both inputs, adding NULL placeholders for the side that has no qualifying match.
A complete customer report should include Dev even though he has not purchased anything. A complete reconciliation may also need the anonymous sale. LEFT, RIGHT and FULL joins differ in which unmatched inputs they preserve.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How LEFT, RIGHT and FULL OUTER JOIN works
LEFT JOIN preserves every qualifying row from the left input. A customer-to-sales left join shows each matched sale and also one placeholder combination for Dev. The right-side sale fields are NULL in that placeholder. RIGHT JOIN preserves the right input instead and is often rewritten by swapping the tables and using LEFT JOIN, which can make a query easier to read without changing the intended relationship.
FULL OUTER JOIN preserves unmatched rows from both inputs. In this example it includes Dev and sale 106 as well as the five matching sales. The unmatched sale has no customer-side id or name, while Dev has no sale-side id or amount. Do not infer an absent sale merely from a NULL amount: Cara’s sale 104 genuinely exists and has an unknown amount. Test the joined primary key when you need evidence of a missing partner.
ON versus WHERE in an outer join
Filter placement changes preservation. LEFT JOIN sales ON relationship AND status='paid' keeps every customer while matching only paid sales. Adding WHERE sales.status='paid' afterwards removes the placeholder for a customer with no paid match, effectively losing those preserved customers. Conditions on the preserved customer itself can still belong in WHERE when the report deliberately restricts the customer population. Read each condition as defining either the population or the eligible match.
MySQL has no native FULL OUTER JOIN. A common emulation combines a left-join branch with unmatched right-side rows using UNION ALL. The second branch must include only right rows with no left match, otherwise matching pairs are duplicated. Ordinary UNION can eliminate legitimately repeated selected values and is not an automatic substitute. Full joins also need careful output labels because a missing partner is different from a nullable field in a partner that actually exists.
MySQL, PostgreSQL, SQL Server and SQLite
LEFT and RIGHT joins are available in PostgreSQL, MySQL and SQL Server; modern SQLite added RIGHT and FULL support in version 3.39. PostgreSQL and SQL Server also support FULL OUTER JOIN. MySQL requires an emulation for FULL. Rewriting RIGHT as a swapped LEFT JOIN remains useful when targeting older SQLite installations.
Check the engine-specific syntax and version requirements in MySQL join grammar, SQLite join support.
Practice task and worked solution
Return all customers with paid-sale ids only, retaining customers with no paid sale. Place the paid condition in the join.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT c.id,c.name,s.id AS paid_sale_id FROM customers AS c
LEFT JOIN sales AS s ON s.customer_id=c.id AND s.status='paid'
ORDER BY c.id,s.id;id | name | paid_sale_id
1 | Ana | 101
2 | Ben | 103
3 | Cara | 105
4 | Dev | NULLPaid status determines eligible right-side matches. Dev still belongs to the left-side customer population, so he gets a placeholder. Pending sales do not appear, and no later WHERE condition rejects Dev’s missing right-side fields.
Compare the paid LEFT JOIN condition in ON with a version that puts s.status='paid' in WHERE. The latter should lose Dev. In PostgreSQL, SQL Server or SQLite 3.39+, run a FULL join and inspect the additional anonymous sale. For MySQL build a left branch plus unmatched-right branch with UNION ALL, checking that matched rows appear only once.
SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows Syntax
LEFT, RIGHT and FULL OUTER JOIN
SELECT c.id AS customer_id,c.name,s.id AS sale_id,s.amount
FROM customers AS c LEFT JOIN sales AS s ON s.customer_id=c.id
ORDER BY c.id,s.id;SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows Summary
LEFT JOINkeeps every row of the left table and fills the right side withNULLs when there's no match.RIGHT JOINpreserves the right table instead; it can always be rewritten as aLEFT JOINwith the tables swapped.FULL OUTER JOINkeeps unmatched rows from both sides; MySQL lacks it and SQLite added it in version 3.39.- A filter on the right table belongs in
ONif unmatched left rows must stay; inWHEREit removes them. - Test the right table's key, such as
s.id IS NULL, to find unmatched rows, not a column that may beNULLanyway.
SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows Examples
Each example shows the code, the output it produces and what each part does.
Every customer with any sales
The left side is customers, so all four customers appear. Dev receives a placeholder because no sale matches him. The anonymous sale is absent because preserving customers does not require preserving unmatched sales.
SELECT c.id AS customer_id,c.name,s.id AS sale_id,s.amount
FROM customers AS c LEFT JOIN sales AS s ON s.customer_id=c.id
ORDER BY c.id,s.id;customer_id | name | sale_id | amount
1 | Ana | 101 | 10
1 | Ana | 102 | 30
2 | Ben | 103 | 20
3 | Cara | 104 | NULL
3 | Cara | 105 | 20
4 | Dev | NULL | NULLCommon SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows Mistakes
A WHERE filter that undoes the LEFT JOIN
A paid-status condition in WHERE removes the NULL placeholder and hides customers with no paid sale. Put that condition in ON when those customers must remain. Also, testing s.amount IS NULL wrongly labels Cara’s real unknown-amount sale as an unmatched row; test s.id instead.
SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows in Real Projects
Customer coverage and reconciliation reports
A customer coverage report can show all customers with their eligible purchases, including people with none. A separate transaction reconciliation can preserve unmatched sales too. Choose the preserved population from the report requirements rather than adding FULL merely because it sounds more complete.
Describe which population must survive before choosing an outer join. A customer coverage report preserves customers; a full reconciliation may preserve unmatched sales too. Keep right-side eligibility filters in ON when they define acceptable matches, and distinguish absent partner keys from nullable fields on a partner that genuinely exists.
SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows FAQ
Is RIGHT JOIN necessary?
No. Many right joins can be expressed as a left join with the inputs swapped, and most teams write only LEFT JOIN so queries read consistently from the main table outwards. RIGHT JOIN is still valid SQL and occasionally convenient when adding a table to an existing query.
Does MySQL implement FULL OUTER JOIN?
No native form is available. Use a correct emulation: a LEFT JOIN combined with UNION ALL and the unmatched rows of a RIGHT JOIN, where the second branch keeps only rows whose left key is NULL, so matched rows aren't repeated.
Why did an outer join lose unmatched rows?
Inspect right-side conditions in WHERE: WHERE s.status = 'paid' rejects the NULL placeholders, turning the outer join back into an inner one. Move the condition into ON, or write WHERE s.status = 'paid' OR s.id IS NULL when you also want to keep customers with no sales at all.
How do I count matches per row with a LEFT JOIN?
Count a right-side column, not *: SELECT c.name, COUNT(s.id) FROM customers c LEFT JOIN sales s ON s.customer_id = c.id GROUP BY c.id, c.name. COUNT(s.id) gives 0 for Dev, while COUNT(*) would count his placeholder row as 1.
Can I chain several LEFT JOINs?
Yes, and each one keeps the rows produced so far. Be careful when a later join is an INNER JOIN on a table reached through an earlier LEFT JOIN: it removes the rows whose earlier match was missing. Keep the whole chain as LEFT JOINs when the first table's rows must all survive.
SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which customer has a NULL paid_sale_id in the ON-filtered LEFT JOIN exercise?
SQL LEFT, RIGHT and FULL OUTER JOIN: Unmatched Rows Quiz
2 questions to check what you learned.
Which side does LEFT JOIN preserve?
- Only the right input
- Only rows that match both inputs
- The left input
Which field should detect an absent sale in this sample?
- The joined sale primary key rather than its nullable amount
- The joined nullable amount because NULL always proves no sale
- The customer primary key because every preserved customer must have a sale