What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Understanding SQL JOINs: INNER, LEFT, RIGHT, and FULL Explained

Explore the fundamental differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN in SQL, and learn when to use each for effective data retrieval.

SQL JOINs are essential for combining rows from two or more tables based on a related column between them. Understanding the nuances of each JOIN type is crucial for writing efficient and accurate queries. This article will demystify INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, providing clear explanations, visual aids, and practical examples to help you master these fundamental SQL operations.

The Foundation: What is a JOIN?

At its core, a JOIN clause is used to combine rows from two or more tables, based on a related column between them. The type of JOIN determines how rows that don't have a match in the other table are handled. We'll use two simple tables, Employees and Departments, to illustrate the concepts throughout this article.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES
(1, 'Alice', 'Smith', 101),
(2, 'Bob', 'Johnson', 102),
(3, 'Charlie', 'Brown', 101),
(4, 'David', 'Davis', NULL); -- David has no department

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(101, 'Sales'),
(102, 'Marketing'),
(103, 'HR'); -- HR has no employees yet

Sample Employees and Departments tables

INNER JOIN: Matching Records Only

An INNER JOIN returns only the rows that have matching values in both tables. If a row in one table does not have a corresponding match in the other table, it is excluded from the result set. This is the most common type of join and is often the default if you just specify JOIN without a type.

flowchart LR
    A["Employees"]
    B["Departments"]
    A -- INNER JOIN on DepartmentID --> C["Matching Rows Only"]
    style C fill:#f9f,stroke:#333,stroke-width:2px

Conceptual diagram of an INNER JOIN

SELECT
    E.FirstName,
    E.LastName,
    D.DepartmentName
FROM
    Employees AS E
INNER JOIN
    Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of an INNER JOIN

In the example above, David Davis (who has NULL for DepartmentID) and the 'HR' department (which has no employees) will not appear in the result because they don't have a match in both tables.

LEFT JOIN (or LEFT OUTER JOIN): All from Left, Matching from Right

A LEFT JOIN (also known as LEFT OUTER JOIN) returns all rows from the left table, and the matching rows from the right table. If there is no match for a row in the left table, the columns from the right table will have NULL values. The 'left' table is the one specified before the LEFT JOIN keyword.

flowchart LR
    A["Employees (Left)"]
    B["Departments (Right)"]
    A -- LEFT JOIN on DepartmentID --> C["All Employees + Matching Departments (NULL if no match)"]
    style C fill:#f9f,stroke:#333,stroke-width:2px

Conceptual diagram of a LEFT JOIN

SELECT
    E.FirstName,
    E.LastName,
    D.DepartmentName
FROM
    Employees AS E
LEFT JOIN
    Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a LEFT JOIN

This query will include David Davis, but his DepartmentName will be NULL because there's no matching department for his DepartmentID (which is NULL). The 'HR' department will still not appear as it has no matching employee.

RIGHT JOIN (or RIGHT OUTER JOIN): All from Right, Matching from Left

A RIGHT JOIN (also known as RIGHT OUTER JOIN) is the inverse of a LEFT JOIN. It returns all rows from the right table, and the matching rows from the left table. If there is no match for a row in the right table, the columns from the left table will have NULL values. The 'right' table is the one specified after the RIGHT JOIN keyword.

flowchart LR
    A["Employees (Left)"]
    B["Departments (Right)"]
    A -- RIGHT JOIN on DepartmentID --> C["All Departments + Matching Employees (NULL if no match)"]
    style C fill:#f9f,stroke:#333,stroke-width:2px

Conceptual diagram of a RIGHT JOIN

SELECT
    E.FirstName,
    E.LastName,
    D.DepartmentName
FROM
    Employees AS E
RIGHT JOIN
    Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a RIGHT JOIN

This query will include the 'HR' department, but its FirstName and LastName columns will be NULL because there are no employees assigned to it. David Davis will not appear in this result because he doesn't have a matching department.

FULL JOIN (or FULL OUTER JOIN): All Records from Both Tables

A FULL JOIN (also known as FULL OUTER JOIN) returns all rows when there is a match in either the left or the right table. It essentially combines the results of both LEFT JOIN and RIGHT JOIN. If a row in the left table has no match in the right, the right-side columns will be NULL. Conversely, if a row in the right table has no match in the left, the left-side columns will be NULL.

flowchart LR
    A["Employees"]
    B["Departments"]
    A -- FULL JOIN on DepartmentID --> C["All Employees + All Departments (NULL if no match on either side)"]
    style C fill:#f9f,stroke:#333,stroke-width:2px

Conceptual diagram of a FULL JOIN

SELECT
    E.FirstName,
    E.LastName,
    D.DepartmentName
FROM
    Employees AS E
FULL OUTER JOIN
    Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a FULL JOIN

This query will return all employees (including David Davis with a NULL department) and all departments (including 'HR' with NULL employee details). It provides a complete picture, showing matches where they exist and NULLs where they don't.

Summary and When to Use Each JOIN

Choosing the correct JOIN type is critical for retrieving the exact data you need. Here's a quick recap:

Visual comparison of SQL JOIN types

• INNER JOIN: Use when you only want records that have a match in both tables.
• LEFT JOIN: Use when you want all records from the left table, plus any matching records from the right table. If no match, right-side columns are NULL.
• RIGHT JOIN: Use when you want all records from the right table, plus any matching records from the left table. If no match, left-side columns are NULL.
• FULL JOIN: Use when you want all records from both tables, showing matches where they exist and NULLs where they don't.