SQL LEFT JOIN Overview

The SQL LEFT JOIN command returns all records from the left table and matching records from the right table. If there is no matching record in the right table, the result will include NULL values for the columns of the right table.

A LEFT JOIN is used to combine rows from two or more tables based on a related column between them. It returns all rows from the left table and the matched rows from the right table. If there is no match, the result includes rows from the left table with NULL values for columns from the right table.

LEFT JOIN Syntax

SELECT column_name(s)
FROM tableA
LEFT JOIN tableB ON tableA.column_name = tableB.column_name;

SQL LEFT JOIN Example

To illustrate the LEFT JOIN, we will use the following unique tables:

Employee Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Country VARCHAR(50),
    Age INT,
    Salary INT,
    DepartmentID INT
);

INSERT INTO Employees (EmployeeID, Name, Country, Age, Salary, DepartmentID)
VALUES
(1, 'Alex Johnson', 'USA', 29, 50000, 201),
(2, 'Maria Garcia', 'Spain', 32, 60000, 202),
(3, 'Liam Smith', 'UK', 27, 45000, NULL),
(4, 'Nina Patel', 'India', 30, 55000, 204),
(5, 'John Doe', 'Canada', 28, 47000, 201);

Department Table

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

INSERT INTO Departments (DepartmentID, DepartmentName, ManagerName, Location)
VALUES
(201, 'HR', 'James Brown', 'New York'),
(202, 'Finance', 'Linda White', 'Madrid'),
(203, 'IT', 'Robert Black', 'San Francisco'),
(204, 'Marketing', 'Emily Davis', 'Mumbai');

Performing a LEFT JOIN

To combine data from these tables, you can use the following SQL query:

SELECT 
    Employees.EmployeeID, 
    Employees.Name, 
    Departments.DepartmentName, 
    Departments.ManagerName, 
    Departments.Location
FROM 
    Employees
LEFT JOIN 
    Departments
ON 
    Employees.DepartmentID = Departments.DepartmentID;

LEFT JOIN Output

This query will return all employees along with their department information. Employees who do not belong to any department (i.e., DepartmentID is NULL) will have NULL values in the DepartmentName, ManagerName, and Location columns.

SQL LEFT JOIN with WHERE Clause Example

To filter results and show only employees from a specific department location, such as ‘Mumbai’, use the following query:

SELECT 
    e.EmployeeID, 
    e.Name, 
    d.DepartmentName, 
    d.ManagerName, 
    d.Location
FROM 
    Employees e
LEFT JOIN 
    Departments d
ON 
    e.DepartmentID = d.DepartmentID
WHERE 
    d.Location = 'Mumbai';

SQL LEFT JOIN with Aliases Example

Using table aliases simplifies the query, especially with long or complex table names:

SELECT 
    e.EmployeeID, 
    e.Name, 
    d.DepartmentName, 
    d.ManagerName, 
    d.Location
FROM 
    Employees e
LEFT JOIN 
    Departments d
ON 
    e.DepartmentID = d.DepartmentID;

Important Points About SQL LEFT JOIN

• LEFT JOIN returns all records from the left table and matching records from the right table.
• If there is no match, the result includes NULL values for columns from the right table.
• LEFT JOIN is used to combine data based on related columns.
• Aliases can make queries more readable and easier to manage.
• Adding a WHERE clause allows for filtering of results based on specific conditions.