LEFT JOIN and RIGHT JOIN

An INNER JOIN only returns rows that match in both tables. But what if you want to keep all rows from one table, even when there is no match in the other? That is exactly what LEFT JOIN and RIGHT JOIN do.

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. When there is no match, the right side is filled with NULLs.

  Left Table        Right Table
 +-----------+     +-----------+
 |           |     |           |
 | ALL rows  |match|  matched  |
 | included  |---->|  rows     |
 |           |     |  only     |
 | unmatched |     |           |
 | rows get  |     | unmatched |
 | NULLs --> |     | rows are  |
 |           |     | excluded  |
 +-----------+     +-----------+

Syntax

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

The keywords LEFT JOIN and LEFT OUTER JOIN are identical.

Example: All Employees with Their Department Details

SELECT
  e.name AS employee_name,
  e.department,
  d.budget,
  d.location
FROM employees e
LEFT JOIN departments d
  ON e.department = d.name;

This returns every employee, even if their department does not exist in the departments table. If there is no match, d.budget and d.location will be NULL.

Finding Unmatched Rows

One of the most powerful uses of LEFT JOIN is finding rows that have no match in the other table. This pattern is sometimes called an anti-join:

SELECT
  e.name AS employee_name,
  e.department
FROM employees e
LEFT JOIN departments d
  ON e.department = d.name
WHERE d.name IS NULL;

This query finds employees whose department does not exist in the departments table. The trick: after the LEFT JOIN, rows with no match have NULL in all right-table columns. Filtering on WHERE d.name IS NULL isolates those unmatched rows.

Practical Example: Customers Who Have Never Ordered

SELECT
  c.name AS customer_name,
  c.email,
  o.id AS order_id
FROM customers c
LEFT JOIN orders o
  ON c.name = o.customer_name
WHERE o.id IS NULL;

This finds customers who have never placed an order -- a common business question.

RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN is the mirror image of a LEFT JOIN. It returns all rows from the right table and matched rows from the left table:

  Left Table        Right Table
 +-----------+     +-----------+
 |           |     |           |
 |  matched  |match| ALL rows  |
 |  rows     |<----|  included |
 |  only     |     |           |
 |           |     | unmatched |
 | unmatched |     | rows get  |
 | rows are  |     | NULLs <-- |
 | excluded  |     |           |
 +-----------+     +-----------+

Syntax

SELECT columns
FROM left_table
RIGHT JOIN right_table
  ON left_table.column = right_table.column;

Example: All Departments with Their Employees

SELECT
  d.name AS department_name,
  d.budget,
  e.name AS employee_name,
  e.salary
FROM employees e
RIGHT JOIN departments d
  ON e.department = d.name;

This returns every department, even if no employees belong to it. Departments without employees will show NULL for the employee columns.

LEFT JOIN vs RIGHT JOIN

In practice, most developers prefer LEFT JOIN because it is easier to read -- the "main" table comes first. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order:

-- These two queries produce identical results:

-- Using RIGHT JOIN
SELECT *
FROM employees e
RIGHT JOIN departments d
  ON e.department = d.name;

-- Equivalent LEFT JOIN (tables swapped)
SELECT *
FROM departments d
LEFT JOIN employees e
  ON d.name = e.department;

LEFT JOIN with Aggregation

You can combine LEFT JOIN with GROUP BY to get counts that include zeros:

SELECT
  d.name AS department_name,
  COUNT(e.name) AS employee_count,
  COALESCE(SUM(e.salary), 0) AS total_salary
FROM departments d
LEFT JOIN employees e
  ON d.name = e.department
GROUP BY d.name
ORDER BY employee_count DESC;

Using COUNT(e.name) instead of COUNT(*) is important here. COUNT(*) would count 1 even for departments with no employees (because the department row exists), while COUNT(e.name) correctly returns 0 when e.name is NULL.

Try It Yourself

1. Write a LEFT JOIN to list all departments and their employees. Which departments, if any, have no employees?

2. Use a LEFT JOIN to find all customers who have never placed an order.

3. Write a query using LEFT JOIN and GROUP BY to show each department name alongside the number of active employees (is_active = true) in that department. Departments with zero active employees should show 0.