LEFT JOIN in SQL

Joins in SQL are used to combine rows from two or more tables based on a related column between them.

The most common types of joins are:

INNER JOIN,
LEFT JOIN,
RIGHT JOIN, and
FULL OUTER JOIN

A LEFT JOIN in SQL is used to retrieve data from two or more tables based on a related column between them. It returns all rows from the left table (table1) and the matching rows from the right table (table2). If there is no match found, NULL values are returned for the columns of the right table.

SQL Syntax

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

Example: Let's use two tables: employees and departments.

SQL

-- Creating the employee table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    department_id INT
);

-- Inserting the provided data
INSERT INTO employees
VALUES 
(1, 'Ashish', 1),
(2, 'Rahul', 2),
(3, 'Priya', 1),
(4, 'Sneha', 3),
(5, 'Shweta', 4);

Table: employees

Employee ID	First Name	Department ID
1	Ashish	1
2	Rahul	2
3	Priya	1
4	Sneha	3
5	Shweta	4

SQL

-- Creating the departments table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- Inserting the provided data
INSERT INTO departments
VALUES 
(1, 'Sales'),
(2, 'Marketing'),
(3, 'HR');

Table: departments

Department_ID	Department_Name
1	Sales
2	Marketing
3	HR

To retrieve all employees and their respective departments (including employees without a department).

SQL

SELECT e.employee_id, e.first_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

The above query returns the following result:

Employee_ID	First_Name	Department_ID	Department_Name
1	Ashish	1	Sales
2	Rahul	2	Marketing
3	Priya	1	Sales
4	Sneha	3	HR
5	Shweta	4	NULL

Explanation:

Ashish, and Priya, are in the Sales department (department_id = 1).
Rahul is in the Marketing department (department_id = 2).
Sneha is in the HR department (department_id = 3).
We did not have department_id 4 in the departments table, so its value is NULL.