SQL - Left Join vs Right Join

SQL OUTER JOINs: LEFT vs. RIGHT JOIN

When querying data from multiple tables, we often need to handle cases where records in one table don’t have a corresponding match in the other. This is where OUTER JOINs are essential. The LEFT JOIN and RIGHT JOIN are the two most common types of outer joins. They allow you to retrieve all records from one table, along with the matched records from a second table. If no match is found, NULL values are returned for the columns of the second table.

How a LEFT JOIN Works

A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table (the first table mentioned), and the matched records from the right table. If a record in the left table has no match in the right table, the result will still include that record, but with NULL values for all columns from the right table.

Syntax

SELECT
    t1.column1, t1.column2,
    t2.column1, t2.column2
FROM
    table1 AS t1
LEFT JOIN
    table2 AS t2 ON t1.common_column = t2.common_column;

Conceptual Example

Let’s consider two tables: employees and departments. The departments table lists all official departments, but some employees might not be assigned to a department yet.

employees table:

+----+-----------+---------------+---------------+
| id | name      | department_id | hire_date     |
+----+-----------+---------------+---------------+
| 1  | Alice     | 101           | 2022-01-15    |
| 2  | Bob       | 102           | 2022-03-20    |
| 3  | Charlie   | 101           | 2023-05-10    |
| 4  | David     | NULL          | 2023-08-01    |
+----+-----------+---------------+---------------+

departments table:

+-----+-------------+
| id  | name        |
+-----+-------------+
| 101 | Engineering |
| 102 | Marketing   |
| 103 | Sales       |
+-----+-------------+

If we want a list of all employees and their department names, we use a LEFT JOIN with employees as the left table.

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

Result: Notice David is included, but his department_name is NULL because his department_id is NULL and doesn’t match any ID in the departments table.

+-----------+-----------------+
| name      | department_name |
+-----------+-----------------+
| Alice     | Engineering     |
| Bob       | Marketing       |
| Charlie   | Engineering     |
| David     | NULL            |
+-----------+-----------------+

How a RIGHT JOIN Works

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a LEFT JOIN. It returns all records from the right table (the second table mentioned), and the matched records from the left table. If a record in the right table has no match in the left table, the result will still include that record, but with NULL values for all columns from the left table.

Syntax

SELECT
    t1.column1, t1.column2,
    t2.column1, t2.column2
FROM
    table1 AS t1
RIGHT JOIN
    table2 AS t2 ON t1.common_column = t2.common_column;

Conceptual Example

Using the same tables, let’s say we want a list of all departments and the names of employees in them. We use a RIGHT JOIN with departments as the right table.

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

Result: Notice the ‘Sales’ department is included, but the employee name is NULL because no employee is assigned to department ID 103.

+---------+-----------------+
| name    | department_name |
+---------+-----------------+
| Alice   | Engineering     |
| Charlie | Engineering     |
| Bob     | Marketing       |
| NULL    | Sales           |
+---------+-----------------+

Key Differences and Best Practices

The fundamental difference is which table’s records are preserved entirely. A LEFT JOIN preserves the left table’s rows, while a RIGHT JOIN preserves the right’s.

Characteristic	LEFT JOIN	RIGHT JOIN
Primary Table	The table on the LEFT of the JOIN keyword	The table on the RIGHT of the JOIN keyword
Unmatched Rows	Includes all rows from the left table, even if unmatched.	Includes all rows from the right table, even if unmatched.
NULL Placement	NULLs appear in columns from the right table.	NULLs appear in columns from the left table.

Best Practice: Prefer LEFT JOIN

Any RIGHT JOIN can be rewritten as a LEFT JOIN by simply swapping the order of the tables. For example, the RIGHT JOIN query above is equivalent to:

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

Many development teams adopt a convention of exclusively using LEFT JOIN. This improves code readability and consistency, as the primary table of focus is always the first one listed in the FROM clause.

Common Pitfalls

A frequent mistake is placing a filter condition for the outer table in the WHERE clause instead of the ON clause. This effectively turns the OUTER JOIN into an INNER JOIN.

Incorrect: This query will NOT return David, because WHERE e.hire_date > '2023-01-01' filters out his NULL result after the join.

-- Incorrectly filters out non-matching rows
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE e.hire_date > '2023-01-01'; -- Condition on LEFT table in WHERE

Correct: Conditions on the outer table should be part of the ON clause if you want to preserve the NULL results.

-- This logic is usually more complex and depends on the goal.
-- For filtering the right table, it's straightforward:
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.name = 'Engineering';