Difference Between Left Join And Right Join In Sql

Understanding the difference between left join and right join in SQL is essential for anyone who works with relational databases, because these two types of outer joins determine how unmatched rows from the participating tables are handled. Mastering this concept lets you write queries that return the exact dataset you need, whether you are building reports, cleaning data, or integrating information from multiple sources. In the sections below we break down the mechanics of each join, illustrate them with clear examples, highlight the key distinctions, and provide practical guidance on when to choose one over the other.

What Are SQL Joins?

In SQL, a join combines rows from two or more tables based on a related column between them. The most common join types are:

• Inner join – returns only rows that have matching values in both tables.
• Outer join – returns matching rows plus some or all non‑matching rows, depending on the variant.
• Cross join – produces the Cartesian product of the tables (every row from the first table paired with every row from the second).

Outer joins come in three flavors: left join, right join, and full outer join. The focus of this article is the contrast between left join and right join, both of which are outer joins that preserve rows from one side of the relationship while optionally including rows from the other side Simple, but easy to overlook..

Left Join Explained

A LEFT JOIN (or LEFT OUTER JOIN) keeps every row from the left table, regardless of whether there is a matching row in the right table. When a match exists, the columns from the right table are filled with the corresponding values; when no match exists, those columns are filled with NULL.

Syntax

SELECT  left_table.column1,
        left_table.column2,
        right_table.columnA,
        right_table.columnB
FROM    left_table
LEFT JOIN right_table
        ON left_table.foreign_key = right_table.primary_key;

Conceptual Flow

1. Start with each row in the left table.
2. Look for a row in the right table where the join condition (ON …) evaluates to true.
3. If a match is found, combine the left‑row values with the right‑row values.
4. If no match is found, still output the left‑row but pad the right‑table columns with NULL.

Example

Suppose we have two tables:

employees

departments

Running a left join that keeps all employees:

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

Result:

It sounds simple, but the gap is usually here.

Notice that Diana’s row appears even though her dept_id is NULL and therefore has no match in departments. The dept_name column shows NULL for her But it adds up..

Right Join Explained

A RIGHT JOIN (or RIGHT OUTER JOIN) does the opposite: it preserves every row from the right table, while rows from the left table appear only when they satisfy the join condition. Unmatched left‑table rows produce NULL values for the left‑table columns.

Syntax

SELECT  left_table.column1,
        left_table.column2,
        right_table.columnA,
        right_table.columnB
FROM    left_table
RIGHT JOIN right_table
        ON left_table.foreign_key = right_table.primary_key;

Conceptual Flow

1. Iterate over each row in the right table.
2. Search for a matching row in the left table using the ON predicate.
3. If a match exists, combine the left‑row and right‑row values.
4. If no match exists, still output the right‑row but fill the left‑table columns with NULL.

Example

Using the same tables as before, a right join that keeps all departments:

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

Result:

The HR department (dept_id = 40) appears even though no employee is assigned to it; the employee‑related columns are NULL. Conversely, Diana (who had a NULL dept_id) does not appear because the right table drives the result set.

Key Differences Between Left Join and Right Join