SQL LEFT & RIGHT JOIN – Keeping Unmatched Rows

LEFT JOIN Syntax

SQL

SELECT columns
FROM left_table
LEFT JOIN right_table ON left_table.col = right_table.col;

-- LEFT OUTER JOIN is identical — OUTER keyword is optional
SELECT columns
FROM left_table
LEFT OUTER JOIN right_table ON left_table.col = right_table.col;

SQL

-- Include ALL employees, even those without a department
SELECT e.id, e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Result:
-- id | name  | dept_name
--  1 | Alice | Marketing
--  2 | Bob   | Engineering
--  3 | Carol | Engineering
--  4 | Dave  | HR
--  5 | Eve   | NULL          ← Eve kept; no matching department

-- Finance is still excluded (it is on the RIGHT side with no match)
-- Finance would appear with LEFT JOIN if departments is the LEFT table

NULLs in LEFT JOIN Results

When a left-table row has no match in the right table, all right-table columns in the result are NULL. You can test these NULLs to identify unmatched rows.

SQL

-- Show employees with their department name (or 'No Department')
SELECT
  e.name,
  COALESCE(d.dept_name, 'No Department') AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Count employees per department, including departments with 0 employees
-- NOTE: swap the table order so departments is on the LEFT
SELECT
  d.dept_name,
  COUNT(e.id) AS employee_count  -- COUNT(e.id) counts non-NULL — gives 0 for empty depts
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.dept_name
ORDER BY employee_count DESC;

⚠️

Use COUNT(column), not COUNT(*), with LEFT JOIN

When counting with a LEFT JOIN, use COUNT(right_table.id) not COUNT(*). COUNT(*) counts all result rows including the NULL-filled ones, inflating the count. COUNT(column) only counts non-NULL values, correctly giving 0 for groups with no matches.

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right table and fills NULLs for unmatched left-table columns.

SQL

-- RIGHT JOIN: keep all departments, even those with no employees
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- Result:
-- name  | dept_name
-- Alice | Marketing
-- Bob   | Engineering
-- Carol | Engineering
-- Dave  | HR
-- NULL  | Finance      ← Finance kept; no matching employee

-- Eve is excluded (she has no department — unmatched on LEFT side)

-- Equivalent LEFT JOIN (swap table order — same result)
SELECT e.name, d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id;

LEFT vs RIGHT: Which to Use?

	LEFT JOIN	RIGHT JOIN
All rows from	Left (first) table	Right (second) table
NULLs for	Unmatched right columns	Unmatched left columns
Rewrite as	—	LEFT JOIN with tables swapped
Preferred?	Yes — use this by default	Avoid — swap tables and use LEFT

💡

Stick to LEFT JOIN

Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Most SQL developers use exclusively LEFT JOIN to keep queries consistent and easier to reason about. Mixing LEFT and RIGHT in the same query is a common source of confusion.

Finding Unmatched Rows (Anti-Join)

The anti-join pattern finds rows in one table that have no corresponding row in another. Use LEFT JOIN and filter where the right-table key IS NULL.

SQL

-- Find employees with no department assigned
SELECT e.id, e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;
-- Returns: Eve (id=5, dept_id=NULL)

-- Find departments with no employees
SELECT d.id, d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;
-- Returns: Finance (id=4)

-- Find customers who have never placed an order
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Find products that have never been ordered
SELECT p.id, p.product_name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

Summary

📋 Summary

LEFT JOIN — All rows from the left table. NULLs for right-table columns when no match.
RIGHT JOIN — All rows from the right table. Equivalent to swapping tables and using LEFT JOIN.
Prefer LEFT JOIN — More readable and consistent. Avoid mixing LEFT and RIGHT in one query.
COALESCE — Use to replace NULLs in output: COALESCE(d.dept_name, 'Unassigned').
COUNT(right_col) — Use column count, not COUNT(*), to avoid inflated counts with LEFT JOIN.
Anti-join pattern — LEFT JOIN + WHERE right_table.id IS NULL finds unmatched rows efficiently.

FAQ

If I add a WHERE clause to a LEFT JOIN, can it become an INNER JOIN?+

Yes — this is a very common mistake. If you write LEFT JOIN departments d ON e.dept_id = d.id WHERE d.dept_name = 'Engineering', the WHERE filters out NULL rows (where d.dept_name is NULL), effectively converting the LEFT JOIN to an INNER JOIN. To keep unmatched rows while filtering the right table, move the condition into the ON clause: LEFT JOIN departments d ON e.dept_id = d.id AND d.dept_name = 'Engineering'.

Can I LEFT JOIN to more than one table?+

Yes. Chain multiple LEFT JOINs: each one keeps all rows from the result so far and adds NULLs for unmatched rows in the next table. Be careful to apply each join to the correct table and check that WHERE clauses on right-table columns do not accidentally eliminate the NULL rows you want to keep.

Is NOT IN or NOT EXISTS better than the anti-join pattern?+

All three can find unmatched rows, but each has trade-offs. LEFT JOIN + IS NULL is often the most performant and index-friendly. NOT IN has a dangerous NULL trap (if the subquery contains a NULL, NOT IN returns no rows). NOT EXISTS is semantically clean and handles NULLs correctly, but is slower on some databases. For production code, LEFT JOIN + IS NULL or NOT EXISTS are the safest choices.

Why does COUNT(*) give wrong results with LEFT JOIN?+

When a department has no employees, the LEFT JOIN adds one NULL-filled row for that department. COUNT(*) counts that row, giving 1 instead of 0. COUNT(e.id) only counts non-NULL values in the employee id column, correctly returning 0 for departments with no employees. Always use COUNT(right_table_column) when counting matches in a LEFT JOIN.