SQL Outer Joins (LEFT, RIGHT, FULL) Explained with Examples + Practice Questions

When to Use Outer Joins

• Use LEFT JOIN to keep all rows from the left table
• Use RIGHT JOIN to keep all rows from the right table
• Use FULL JOIN to keep all rows from both tables

Outer joins are used when you do not want to lose unmatched data.

Example Dataset

Table: employees

Table: departments

LEFT JOIN

Returns:

• All rows from left table (employees)
• Matching rows from right table
• If no match → NULL

Example

sql
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;

RIGHT JOIN

Returns:

• All rows from right table (departments)
• Matching rows from left table
• If no match → NULL

Example

sql
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.id;

FULL JOIN

Returns:

• All rows from both tables
• Matches where possible
• NULL where no match

Example

sql
SELECT e.name, d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.id;

LEFT vs RIGHT vs FULL

Common Use Cases

• LEFT JOIN → find missing data
• RIGHT JOIN → reverse analysis
• FULL JOIN → full comparison

Finding Unmatched Rows (Important)

Employees without department

sql
SELECT e.name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id
WHERE d.id IS NULL;

Departments without employees

sql
SELECT d.department_name
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.id
WHERE e.id IS NULL;

Key Concepts

• INNER JOIN removes unmatched rows
• OUTER JOIN keeps unmatched rows
• NULL indicates no match
• LEFT JOIN is most commonly used

Common Questions (FAQ)

What is LEFT JOIN

Returns all rows from left table and matching rows from right table.

What is RIGHT JOIN

Returns all rows from right table and matching rows from left table.

What is FULL JOIN

Returns all rows from both tables, matched or not.

When to use LEFT JOIN

When you want to find missing or unmatched data.

Internal Learning Links

• Learn INNER JOIN
• Learn WHERE filtering
• Learn GROUP BY

Practice CTA

Practice SQL JOIN problems on Schoolabe to master real-world data relationships.

SQLite Note

SQLite does not support RIGHT JOIN or FULL JOIN directly. In the exercises below, we use equivalent queries (RIGHT JOIN = LEFT JOIN with tables swapped; FULL JOIN = UNION of LEFT + RIGHT).