PostgreSQL LEFT JOIN vs RIGHT JOIN: Complete Guide

LEFT and RIGHT JOINs are outer joins - they keep all rows from one table and add matching rows from another. If no match exists, columns from the other table are NULL. Understanding when to use each is crucial for writing correct queries.

What You’ll Learn

This guide covers:

  • LEFT JOIN: Keeping all left table rows
  • RIGHT JOIN: Keeping all right table rows
  • NULL handling in outer joins
  • When to use LEFT vs RIGHT
  • Common business scenarios
  • Performance considerations

Understanding the Difference

The key difference is which table’s rows are always kept.

LEFT JOIN keeps all rows from the left (first) table:

SELECT e.emp_id, e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

All employees appear, even if they have no department (dept_name would be NULL).

RIGHT JOIN keeps all rows from the right (second) table:

SELECT e.emp_id, e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

All departments appear, even if they have no employees (emp_id and first_name would be NULL).

LEFT JOIN Examples

Basic LEFT JOIN

Get all employees and their departments (some employees might have no department).

SELECT
    e.emp_id,
    e.first_name,
    e.last_name,
    d.dept_name,
    d.location
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.last_name;

Result (assuming some employees have NULL dept_id):

emp_id | first_name | last_name |    dept_name    | location
-------+------------+-----------+-----------------+----------
      1 | James      | Wilson    | Sales           | New York
      2 | Mary       | Johnson   | Sales           | New York
     30 | Unknown    | Unassigned| (null)          | (null)

The key: Row 30 appears even though it has no department. INNER JOIN would exclude it.

LEFT JOIN with WHERE to Find Unmatched Rows

Find all departments that have no employees.

SELECT
    d.dept_id,
    d.dept_name,
    d.location
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL
ORDER BY d.dept_name;

The WHERE e.emp_id IS NULL finds rows where the join found no match.

Result:

dept_id |   dept_name    | location
--------+----------------+-----------
      5 | Advanced       | Seattle
      6 | Research       | Boston

This pattern is powerful - use LEFT JOIN then filter for NULL to find unmatched data.

Aggregate LEFT JOIN

Count employees per department (including departments with zero employees).

SELECT
    d.dept_id,
    d.dept_name,
    d.location,
    COUNT(e.emp_id) as total_employees,
    AVG(e.salary) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name, d.location
ORDER BY total_employees DESC;

Result:

dept_id |    dept_name    | location      | total_employees | avg_salary
--------+-----------------+---------------+-----------------+------------
      4 | Operations      | Chicago       |              10 | 68,700.00
      2 | Engineering     | San Francisco |               8 | 97,625.00
      3 | Marketing       | Los Angeles   |               6 | 78,833.33
      1 | Sales           | New York      |               6 | 74,166.67
      5 | Advanced        | Seattle       |               0 | (null)
      6 | Research        | Boston        |               0 | (null)

Compare this to INNER JOIN:

  • INNER JOIN: Returns only departments with employees (4 rows)
  • LEFT JOIN: Returns all departments, including empty ones (6 rows)

The departments with zero employees have NULL average salary (because AVG of zero rows is NULL).

LEFT JOIN with Multiple Conditions

Get employees and their salary history, including employees with no salary records.

SELECT
    e.emp_id,
    e.first_name,
    e.last_name,
    s.salary,
    s.effective_date
FROM employees e
LEFT JOIN salaries s ON e.emp_id = s.emp_id
ORDER BY e.last_name, s.effective_date DESC;

Result:

emp_id | first_name | last_name | salary  | effective_date
-------+------------+-----------+---------+----------------
      1 | James      | Wilson    | 75000   | 2023-06-01
      1 | James      | Wilson    | 72000   | 2022-01-01
      1 | James      | Wilson    | 70000   | 2020-01-15
      2 | Mary       | Johnson   | 78000   | 2023-01-15
     30 | Unknown    | Employee  | (null)  | (null)

Employee 30 appears even with no salary history.

RIGHT JOIN Examples

Basic RIGHT JOIN

Get all departments and their employees.

SELECT
    d.dept_id,
    d.dept_name,
    e.emp_id,
    e.first_name,
    e.last_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.last_name;

Result:

dept_id |    dept_name    | emp_id | first_name | last_name
--------+-----------------+--------+------------+-----------
      1 | Sales           |      1 | James      | Wilson
      1 | Sales           |      2 | Mary       | Johnson
      2 | Engineering     |      7 | David      | Miller
      2 | Engineering     |      8 | Jennifer   | Martinez
      5 | Advanced        | (null) | (null)     | (null)
      6 | Research        | (null) | (null)     | (null)

All departments appear, with NULL for employees if a department is empty.

RIGHT JOIN vs LEFT JOIN

These queries give identical results - just written differently:

-- Query 1: LEFT JOIN
SELECT e.emp_id, e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- Query 2: RIGHT JOIN (equivalent)
SELECT e.emp_id, e.first_name, d.dept_name
FROM departments d
RIGHT JOIN employees e ON d.dept_id = e.dept_id;

Both keep all employee rows and add matching department data. The difference is table order in the FROM/JOIN clauses.

Best Practice: Most developers prefer LEFT JOIN and reverse table order rather than use RIGHT JOIN. It’s clearer which table’s rows are kept.

Instead of:

FROM a RIGHT JOIN b ON ...

Write:

FROM b LEFT JOIN a ON ...

RIGHT JOIN with Filtering

Find departments whose manager is not assigned.

SELECT
    d.dept_id,
    d.dept_name,
    d.manager_id,
    m.first_name,
    m.last_name
FROM employees m
RIGHT JOIN departments d ON d.manager_id = m.emp_id
WHERE m.emp_id IS NULL
ORDER BY d.dept_name;

Result:

dept_id |   dept_name    | manager_id | first_name | last_name
--------+----------------+------------+------------+-----------
      5 | Advanced       |     (null) | (null)     | (null)
      6 | Research       |     (null) | (null)     | (null)

LEFT JOIN vs RIGHT JOIN: When to Use Each

Use LEFT JOIN When:

  1. You want all data from the first table

    -- All employees, whether or not assigned to a project
FROM employees e
LEFT JOIN projects p ON e.emp_id = p.assigned_to

  2. You’re finding unmatched records

    -- Products never ordered
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE o.order_id IS NULL

  3. You’re building reports that include everything

    -- All customers and their orders (including those who haven't ordered)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

Use RIGHT JOIN When:

Honestly, avoid RIGHT JOIN. Almost every use case is clearer with LEFT JOIN by reversing the table order.

Instead of:

-- Avoid: RIGHT JOIN is confusing
SELECT * FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Write:

-- Better: LEFT JOIN is clearer
SELECT * FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

Both do the same thing, but LEFT JOIN makes it obvious which table’s rows are kept.

NULL Handling in Outer Joins

Understanding NULL in Outer Joins

When a match doesn’t exist, columns from the non-kept table are NULL:

SELECT
    e.emp_id,
    e.first_name,
    d.dept_id,
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

If employee has dept_id = NULL:

  • e.emp_id, e.first_name have values
  • d.dept_id, d.dept_name are NULL

Checking for Unmatched Rows

Use IS NULL to find unmatched records:

-- Employees with no department assignment
SELECT e.emp_id, e.first_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

Or check any non-null column:

WHERE e.dept_id IS NULL           -- The join column itself
WHERE d.dept_name IS NULL         -- Any column from the joined table

COALESCE to Replace NULL

Display meaningful defaults instead of NULL:

SELECT
    e.emp_id,
    e.first_name,
    COALESCE(d.dept_name, 'Unassigned') as department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.first_name;

Result:

emp_id | first_name |    department
-------+------------+---------------
      1 | James      | Sales
      2 | Mary       | Sales
     30 | Unknown    | Unassigned

CASE Expression for Complex Logic

SELECT
    e.first_name,
    e.salary,
    CASE
        WHEN d.dept_name IS NULL THEN 'No Department'
        WHEN e.salary < 75000 THEN d.dept_name || ' (Under-compensated)'
        ELSE d.dept_name
    END as department_status
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Real-World Business Scenarios

Customer Order Analysis

Find customers who haven’t ordered in the last 30 days.

SELECT
    c.customer_id,
    c.customer_name,
    c.signup_date,
    MAX(o.order_date) as last_order_date,
    CURRENT_DATE - MAX(o.order_date) as days_since_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.signup_date
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '30 days'
   OR MAX(o.order_date) IS NULL
ORDER BY days_since_order DESC;

Finds customers with no recent orders for targeted re-engagement campaigns.

Inventory Management

Products with inventory vs without.

SELECT
    p.product_id,
    p.product_name,
    COALESCE(i.quantity_on_hand, 0) as stock,
    COALESCE(i.last_restock_date, 'Never') as restocked
FROM products p
LEFT JOIN inventory i ON p.product_id = i.product_id
WHERE i.quantity_on_hand = 0 OR i.quantity_on_hand IS NULL
ORDER BY p.product_name;

Shows out-of-stock products.

Employee Performance Review Coverage

Employees who haven’t had performance reviews.

SELECT
    e.emp_id,
    e.first_name,
    e.last_name,
    COALESCE(MAX(r.review_date), 'No Review') as last_review,
    CURRENT_DATE - MAX(r.review_date) as days_since_review
FROM employees e
LEFT JOIN performance_reviews r ON e.emp_id = r.emp_id
GROUP BY e.emp_id, e.first_name, e.last_name
HAVING MAX(r.review_date) IS NULL
   OR (CURRENT_DATE - MAX(r.review_date) > INTERVAL '365 days')
ORDER BY e.last_name;

Identifies employees needing reviews.

Common Errors & Solutions

Error: Unexpected NULL values in results

Problem:

SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering';

Expected to see all employees, but get fewer rows.

Explanation:

The WHERE clause filters out rows where d.dept_name is NULL. The LEFT JOIN keeps all employees, but WHERE then removes employees with no department.

Solution:

Move the condition to ON:

SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
    AND d.dept_name = 'Engineering';

Now the condition affects which department rows are joined (not filtering results).

Error: “right side of join doesn’t match left side”

Problem:

-- Unbalanced NULL handling
SELECT COUNT(e.emp_id), COUNT(d.dept_id)
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Gets different counts if some employees have no department.

Solution:

This isn’t actually an error - it’s the expected behavior. Just understand:

COUNT(e.emp_id) as employees_total,      -- All 30 employees
COUNT(d.dept_id) as matched_departments  -- Fewer if some have no dept

If you want total rows returned:

COUNT(*) as total_rows  -- Equals COUNT(e.emp_id) for LEFT JOIN

Performance Issue: LEFT JOIN causing slow queries

Problem:

SELECT e.*, d.*, p.*
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON d.dept_id = p.dept_id;

Query is slow and returns many duplicate rows.

Solution:

  1. Select only needed columns:

    SELECT e.emp_id, e.first_name, d.dept_name, p.project_name

  2. Use EXPLAIN to check join performance:

    EXPLAIN ANALYZE
SELECT ...

  3. Ensure indexes exist on join columns:

    CREATE INDEX idx_employees_dept_id ON employees(dept_id);

Performance Tips

1. LEFT JOIN is Slower Than INNER JOIN

Only use LEFT JOIN when you need unmatched rows:

-- Fast: INNER JOIN (all employees have departments)
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- Slower: LEFT JOIN (unnecessary if no employees lack departments)
SELECT * FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

2. Index Join Columns

CREATE INDEX idx_employees_dept_id ON employees(dept_id);
CREATE INDEX idx_departments_dept_id ON departments(dept_id);

3. Use IS NULL Checks Efficiently

-- Faster: PostgreSQL optimizes IS NULL with indexes
WHERE d.dept_id IS NULL;

-- Slower: Full table scan
WHERE d.dept_id = NULL;  -- This doesn't work in SQL!

4. EXPLAIN ANALYZE LEFT JOINs

EXPLAIN ANALYZE
SELECT * FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Look for “Hash Join” or “Merge Join” in output.

FAQ

Q: Should I use LEFT or RIGHT JOIN? A: Prefer LEFT JOIN by reversing table order. It’s clearer which table’s rows are kept.

Q: What does NULL mean in a LEFT JOIN? A: The join found no matching row in the right table for this row from the left table.

Q: Can I use WHERE to filter left table rows in an outer join? A: Yes, but conditions on the left table work. Conditions on the right table should go in ON, not WHERE.

Q: How do I find unmatched rows? A: Use LEFT/RIGHT JOIN then WHERE right_column IS NULL.

Q: Does LEFT JOIN always return more rows than INNER JOIN? A: Not necessarily the same rows, but it returns all left table rows. INNER JOIN returns only matching rows.

Q: Can I LEFT JOIN the same table multiple times? A: Yes, useful for hierarchies: FROM e1 LEFT JOIN e2 ON e1.manager_id = e2.emp_id LEFT JOIN e3 ON e2.manager_id = e3.emp_id

Continue learning about joins:

LEFT and RIGHT JOINs are essential for reports and finding gaps in data. Master them and you’ll write more accurate queries.