Outer joins keep rows that have no match, filling the missing side with NULLs. Use them whenever “no related rows” is a valid answer.

LEFT JOIN

SQL

SELECT d.name AS department, e.name AS employee
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_id, e.name;

Output

department  | employee
------------+---------
Engineering | Asha
Engineering | Karan
Engineering | Meera
Engineering | Ravi
Sales       | Arjun
Sales       | Neha
Sales       | Priya
HR          | Sara
HR          | Vikram
Marketing   | NULL

Marketing appears with a NULL employee: every left row survives. Counting per department must count a column from the right side:

SQL

SELECT d.name, COUNT(e.emp_id) AS headcount, COUNT(*) AS wrong_count
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.name
ORDER BY d.dept_id;

Output

name        | headcount | wrong_count
------------+-----------+------------
Engineering | 4         | 4
Sales       | 3         | 3
HR          | 2         | 2
Marketing   | 0         | 1

RIGHT and FULL OUTER JOIN

A RIGHT JOIN B is B LEFT JOIN A. FULL OUTER JOIN keeps unmatched rows from both sides. SQLite supports both since 3.39 (2022); MySQL has no FULL OUTER JOIN.

SQL

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON d.dept_id = e.dept_id
ORDER BY d.dept_id NULLS LAST, e.emp_id;

Output

employee | department
---------+------------
Asha     | Engineering
Ravi     | Engineering
Meera    | Engineering
Karan    | Engineering
Priya    | Sales
Arjun    | Sales
Neha     | Sales
Vikram   | HR
Sara     | HR
NULL     | Marketing
Dev      | NULL

SQL

-- MySQL workaround for FULL OUTER JOIN: LEFT JOIN ∪ the right-only rows
SELECT e.name AS employee, d.name AS department
FROM employees e LEFT JOIN departments d ON d.dept_id = e.dept_id
UNION ALL
SELECT NULL, d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)
ORDER BY department, employee;

Output

employee | department
---------+------------
Dev      | NULL
Asha     | Engineering
Karan    | Engineering
Meera    | Engineering
Ravi     | Engineering
Sara     | HR
Vikram   | HR
NULL     | Marketing
Arjun    | Sales
Neha     | Sales
Priya    | Sales

ON vs WHERE in an outer join

List every customer with their delivered orders. The filter on the right table must go in ON — in WHERE it removes the NULL rows and silently turns the query into an inner join.

SQL

-- filter in ON: all 5 customers kept
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
ORDER BY c.customer_id, o.order_id;

Output

name   | order_id
-------+---------
Ananya | 101
Ananya | 103
Rahul  | 102
Rahul  | 106
Sneha  | NULL
Imran  | NULL
Kavya  | NULL

SQL

-- filter in WHERE: Sneha, Imran and Kavya disappear
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
ORDER BY c.customer_id, o.order_id;

Output

name   | order_id
-------+---------
Ananya | 101
Ananya | 103
Rahul  | 102
Rahul  | 106

What is the difference between LEFT JOIN and INNER JOIN?

How do you emulate FULL OUTER JOIN in MySQL?