Learn SQL LEFT OUTER JOIN with Examples

 

A Left Outer Join (often referred to simply as a Left Join) is a type of join in SQL that returns all records from the left table (the first table listed in the query) and the matched records from the right table (the second table listed). If there is no match, the result is NULL on the side of the right table. This join is useful when you want to include all records from the left table, regardless of whether there is a corresponding match in the right table.

The basic syntax for a Left Outer Join is as follows:

SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON table1.common_column = table2.common_column;

Explanation:

• SELECT columns: This part of the query specifies the columns that you want to retrieve.
• FROM table1: This part specifies the left table from which to retrieve the data.
• LEFT OUTER JOIN table2: This part specifies the right table to be joined with the left table.
• ON table1.common_column = table2.common_column: This part specifies the condition for joining the two tables, usually based on a common column.

 

SQL LEFT OUTER JOIN vs INNER JOIN vs RIGHT OUTER JOIN vs FULL OUTER JOIN

• Displayed: The records will appear in the result set.
• Optional: The records may or may not appear in the result set, based on whether a matching record is found.
• Ignored: The records will not appear in the result set.
• Displayed with NULLs: The records will appear in the result set, but with NULL values where no match is found.

Here is an image showcasing different types of SQL JOINS:

 

Lab Environment (Sample Tables for Examples)

Let us create some sample tables which we will use throughout our examples:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

INSERT INTO employees (employee_id, name, department_id) VALUES
(1, 'Aarav', 101),
(2, 'Vivaan', 102),
(3, 'Aditya', 103),
(4, 'Sai', NULL),
(5, 'Diya', 101),
(6, 'Ishaan', 104),
(7, 'Mira', NULL);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name) VALUES
(101, 'Human Resources'),
(102, 'Finance'),
(103, 'IT'),
(105, 'Marketing');

 

Simple Example - Using Left Outer Join

Here is how a Left Outer Join can be used to retrieve all employees and their corresponding departments, including employees who may not be assigned to a department:

SELECT employees.name, departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.department_id;

Result:

• All employees are listed regardless of whether they have a corresponding department.
• Employees Sai and Mira have NULL as their department because they do not have a department_id.
• Employee Ishaan has a department_id (104) that does not exist in the departments table, so department_name is NULL.

 

Handling NULLs in SQL LEFT OUTER JOIN

Example 1: Using COALESCE to Replace NULL Values

Assuming we want to replace NULL values in the department_name column with the text "No Department".

SELECT 
    employees.name, 
    COALESCE(departments.department_name, 'No Department') AS department_name
FROM 
    employees
LEFT OUTER JOIN 
    departments
ON 
    employees.department_id = departments.department_id;

COALESCE function checks if departments.department_name is NULL. If it is, it returns 'No Department'. Otherwise, it returns the actual department_name.

 

Example 2: Using IS NULL to Filter Records with NULL Values

Assuming we want to find employees who do not belong to any department:

SELECT 
    employees.name
FROM 
    employees
LEFT OUTER JOIN 
    departments
ON 
    employees.department_id = departments.department_id
WHERE 
    departments.department_id IS NULL;

The IS NULL condition filters out rows where departments.department_id is NULL, indicating that there is no matching department for these employees.

 

Example 3: Using IS NOT NULL to Filter Records with Non-NULL Values

Now let's say we want to find employees who are assigned to a department.

SELECT 
    employees.name, 
    departments.department_name
FROM 
    employees
LEFT OUTER JOIN 
    departments
ON 
    employees.department_id = departments.department_id
WHERE 
    departments.department_id IS NOT NULL;

The IS NOT NULL condition filters out rows where departments.department_id is NULL, showing only employees with a department.

 

Example 4: Using IFNULL (MySQL) or NVL (Oracle) to Handle NULL Values

What if we want to use database-specific functions to handle NULL values:

MySQL Query Using IFNULL:

SELECT 
    employees.name, 
    IFNULL(departments.department_name, 'No Department') AS department_name
FROM 
    employees
LEFT OUTER JOIN 
    departments
ON 
    employees.department_id = departments.department_id;

Oracle Query Using NVL:

SELECT 
    employees.name, 
    NVL(departments.department_name, 'No Department') AS department_name
FROM 
    employees
LEFT OUTER JOIN 
    departments
ON 
    employees.department_id = departments.department_id;

IFNULL and NVL are similar to COALESCE but are specific to MySQL and Oracle, respectively. They replace NULL values with a specified value.

 

Advanced Concepts in SQL LEFT OUTER JOIN

Diving deeper into SQL LEFT OUTER JOIN allows us to explore advanced concepts, such as its implementation in subqueries and executing multiple LEFT OUTER JOINs in a single query. These advanced techniques further enhance the flexibility and power of SQL queries. We’ll demonstrate these concepts using the Employees and Departments tables.

 

Using LEFT OUTER JOIN in Subqueries

We want to find employees who have worked on the most recent project in their department. Here, the projects table contains information about projects and their completion dates.

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    department_id INT,
    project_name VARCHAR(50),
    completion_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO projects (project_id, department_id, project_name, completion_date) VALUES
(1, 101, 'Project A', '2024-01-10'),
(2, 102, 'Project B', '2024-03-15'),
(3, 103, 'Project C', '2024-02-20'),
(4, 101, 'Project D', '2024-05-22'),
(5, 104, 'Project E', '2024-04-30');

Additionally we need to ensure all department IDs in the projects table are present in the departments table. So for the data we are using in this lab, the department_id 104 must be added to the departments table.

INSERT INTO departments (department_id, department_name) VALUES
(104, 'Operations');

SQL Query:

SELECT 
    e.name, 
    p.project_name, 
    p.completion_date
FROM 
    employees e
LEFT OUTER JOIN (
    SELECT 
        p1.department_id, 
        p1.project_name, 
        p1.completion_date
    FROM 
        projects p1
    INNER JOIN (
        SELECT 
            department_id, 
            MAX(completion_date) AS max_date
        FROM 
            projects
        GROUP BY 
            department_id
    ) p2 
    ON 
        p1.department_id = p2.department_id 
        AND p1.completion_date = p2.max_date
) p
ON 
    e.department_id = p.department_id;

• Subquery p2: Selects the maximum completion_date for each department_id.
• Subquery p1: Joins with p2 to get the project details for the latest projects per department.
• Main Query: Performs a LEFT OUTER JOIN between employees and the subquery p to include all employees and their latest project details, if available.

Result:

 

Multiple LEFT OUTER JOINs in a Query

To demonstrate this we want to list all employees, their department names, and the names of their managers. The managers table contains information about department managers.

CREATE TABLE managers (
    manager_id INT PRIMARY KEY,
    department_id INT,
    manager_name VARCHAR(50),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO managers (manager_id, department_id, manager_name) VALUES
(1, 101, 'Dr. Sharma'),
(2, 102, 'Ms. Nair'),
(3, 103, 'Mr. Singh'),
(4, 105, 'Ms. Gupta');

SQL Query:

SELECT 
    e.name AS employee_name, 
    d.department_name, 
    m.manager_name
FROM 
    employees e
LEFT OUTER JOIN 
    departments d
ON 
    e.department_id = d.department_id
LEFT OUTER JOIN 
    managers m
ON 
    e.department_id = m.department_id;

• The first LEFT OUTER JOIN retrieves department names for employees.
• The second LEFT OUTER JOIN retrieves manager names for employees.

Result:

 

Frequently Asked Questions and Answers on SQL Left Outer Join

 

Summarizing Key Takeaways

• Understanding of LEFT OUTER JOIN: LEFT OUTER JOIN is instrumental in combining rows from both participating tables even if there are no matching rows in the right table, keeping all records from the left table.
• Handling NULLs: LEFT OUTER JOIN manages the absence of matching records by introducing NULL values where matches are not found, ensuring that the dataset remains comprehensive.
• Flexibility in Queries: Incorporating LEFT OUTER JOIN into queries offers flexibility and robustness, allowing for the retrieval of inclusive datasets and accommodating for disparities in table relationships.
• Advanced Usage: Using LEFT OUTER JOIN in subqueries and multiple LEFT OUTER JOINs in a single query enables complex data retrieval and manipulation, providing a powerful tool for more advanced SQL operations.
• Comparison with Other Joins: Understanding the differences and suitable use-cases for LEFT OUTER JOIN in comparison to INNER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN is crucial for effective database querying and design.

Here are some resources which can be used to learn more:

• Microsoft SQL Server LEFT JOIN