SQL - Left Join

SQL - The LEFT JOIN

1. What is a LEFT JOIN?

The LEFT JOIN (also known as LEFT OUTER JOIN) is a powerful tool for combining rows from two tables. It returns all records from the left table (the first one mentioned), and the matched records from the right table (the second one mentioned). If there is no match for a row from the left table in the right table, the result will contain NULL values for all columns from the right table.

Think of it as: ‘Give me everything from the left table, and bring along any matching information you can find in the right table.‘

2. Syntax of a LEFT JOIN

SELECT
    table1.column1,
    table1.column2,
    table2.column1
FROM
    table1
LEFT JOIN
    table2 ON table1.matching_column = table2.matching_column;

• table1 is the left table.
• table2 is the right table.
• The ON clause specifies the condition used to match rows between the two tables, typically a primary key-foreign key relationship.

3. Practical Example: Employees and Departments

Let’s create two tables: Employees and Departments. We’ll see how a LEFT JOIN can connect them.

-- The Employees table
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

INSERT INTO Employees VALUES
    (1, 'Alice', 101),
    (2, 'Bob', 102),
    (3, 'Charlie', 101),
    (4, 'David', NULL); -- David is not assigned to a department

-- The Departments table
CREATE TABLE Departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

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

Now, let’s perform a LEFT JOIN to list all employees and their department names.

SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    Employees AS e
LEFT JOIN
    Departments AS d ON e.department_id = d.id;

Pro-Tip: Using table aliases (like e for Employees and d for Departments) makes your queries shorter and more readable.

4. Understanding the Result Set

The query above will produce the following result:

employee_name	department_name
Alice	Sales
Bob	Engineering
Charlie	Sales
David	NULL

Key Observations:

• All four employees from the Employees (left) table are included in the result.
• Alice, Bob, and Charlie found a matching department_id in the Departments table, so their department names are shown.
• David has a NULL department_id, so there was no match. The department_name column is NULL for him.
• The ‘Marketing’ department is not in the result, because no employee is linked to it, and it was in the right table.

5. Common Use Case: Finding Unmatched Records

A very common and powerful use of LEFT JOIN is to find records in the left table that do not have a match in the right table. This is done by adding a WHERE clause to filter for NULL values on a column from the right table.

Example: Find employees without a department

SELECT
    e.name AS employee_name
FROM
    Employees AS e
LEFT JOIN
    Departments AS d ON e.department_id = d.id
WHERE
    d.id IS NULL;

This query first joins the tables, then the WHERE clause keeps only the rows where the department’s ID is NULL, which only happens for employees who have no match. The result would be:

employee_name
David

6. LEFT JOIN vs. INNER JOIN

The key difference is how they handle non-matching rows:

• LEFT JOIN: Keeps all rows from the left table, regardless of whether they have a match in the right table.
• INNER JOIN: Only keeps rows where the join condition is met in both tables. It filters out non-matching rows from both sides.

If we had used an INNER JOIN in our first example, David would have been excluded from the result because he has no matching department.

-- Using INNER JOIN, David will be missing from the results.
SELECT e.name, d.name
FROM Employees AS e
INNER JOIN Departments AS d ON e.department_id = d.id;