SQL LEFT JOIN

Introduction

In this chapter, we will focus on the LEFT JOIN operation in SQL. The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match. This chapter will cover the definition, syntax, and provide examples to help you understand how to use the LEFT JOIN effectively.

What is a LEFT JOIN?

The LEFT JOIN keyword returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the side of the right table.

Syntax for LEFT JOIN

Basic Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

• columns: The columns you want to retrieve.
• table1: The left table you want to join.
• table2: The right table you want to join.
• table1.column = table2.column: The condition that specifies how the tables should be joined.

Step-by-Step Example

Sample Tables

First, let's create two sample tables named employees and departments.

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

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);

Using LEFT JOIN

To retrieve all employees and their department names (including those without a department):

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Output

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
Ravi	Verma	NULL

In this example, all employees are included in the result set. Employees without a matching department_id in the departments table have NULL in the department_name column.

Filtering with LEFT JOIN

You can also use additional filtering conditions with LEFT JOIN.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
WHERE employees.salary > 55000;

Output

first_name	last_name	department_name
Sita	Patel	Finance
Priya	Sharma	Finance

In this example, only the employees with a salary greater than 55000 are included in the result set, along with their department names.

Using LEFT JOIN with Aggregate Functions

You can also use aggregate functions with LEFT JOIN to perform calculations on the joined tables.

SELECT departments.department_name, COUNT(employees.id) AS employee_count
FROM departments
LEFT JOIN employees ON departments.department_id = employees.department_id
GROUP BY departments.department_name;

Output

department_name	employee_count
HR	2
Finance	2
IT	0

In this example, the query returns the number of employees in each department, including departments with no employees.

Multiple Table LEFT JOIN

You can join more than two tables using LEFT JOIN.

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

INSERT INTO projects (project_id, project_name, department_id)
VALUES
(1, 'Project A', 1),
(2, 'Project B', 2),
(3, 'Project C', 3);

SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
LEFT JOIN projects ON departments.department_id = projects.department_id;

Output

first_name	last_name	department_name	project_name
Ramesh	Kumar	HR	Project A
Arjun	Singh	HR	Project A
Sita	Patel	Finance	Project B
Priya	Sharma	Finance	Project B
Ravi	Verma	NULL	NULL

In this example, we join the employees, departments, and projects tables to retrieve employees along with their department names and project names.

Conclusion

The LEFT JOIN is used for combining rows from two or more tables based on a related column and including all rows from the left table. This chapter covered the basic syntax for LEFT JOIN, provided examples to illustrate its use, and demonstrated how to filter and join multiple tables. Understanding how to use LEFT JOIN effectively will greatly enhance your ability to query and analyze data across multiple tables in a relational database. In the next chapter, we will explore RIGHT JOIN with examples.