SQL LEFT JOIN

SQL LEFT JOIN command returns all records from the left table and matching records from the right table. 

LEFT JOIN in SQL 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 matching records from the right table.

If there is no matching record in the right table, then the records from the right table will contain NULL values.

That means that, if a certain row is present in the left table but not in the right, the result will include this row but with a NULL value in each column from the right. If a record from the right table is not on the left, it will not be included in the result.

LEFT JOIN Venn Diagram

This VENN diagram shows how a LEFT JOIN works.

The LEFT JOIN Syntax is:

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

SQL LEFT JOIN Example 

Let’s look at an example of LEFT JOIN in SQL to understand it better.

Let’s consider two tables Emp containing details of the Employee working in the particular department, and department table containing the details of the department

Employee Table

Query:

CREATE TABLE Emp (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    Country VARCHAR(50),
    Age INT,
    Salary INT,
    department_id INT
);

INSERT INTO Emp (EmpID, Name, Country, Age, Salary, department_id)
VALUES (1, 'Shubham', 'India', 23, 30000, 101),
       (2, 'Aman', 'Australia', 21, 45000, 102),
       (3, 'Naveen', 'Sri Lanka', 24, 40000, 103),
       (4, 'Aditya', 'Austria', 21, 35000, 104),
       (5, 'Nishant', 'Spain', 22, 25000, 101);

Output:

Department Table 

Query:

  
CREATE TABLE department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    department_head VARCHAR(50),
    location VARCHAR(50)
);

INSERT INTO department (department_id, department_name, department_head, location) 
VALUES (101, 'Sales', 'Sarah', 'New York'),
       (102, 'Marketing', 'Jay', 'London'),
       (103, 'Finance', 'Lavish', 'San Francisco'),
       (104, 'Engineering', 'Kabir', 'Bangalore');
Select * from department;

Output:

LEFT JOIN in SQL Example

To perform left-join on  these two tables we will use the following SQL query :

SELECT Emp.EmpID, Emp.Name, department.
department_name, department.department_head, 
department.location 
FROM Emp
LEFT JOIN department ON Emp.department_id = department.department_id;

Output:

As left join gives the matching rows and the rows that are present in the left table but not in the right table. Here in this example, we see that the employees that do not work in a particular department, i.e., having dept no values as [NULL], contain [NULL] values of dept name and location after the left join.

SQL LEFT JOIN with WHERE Clause Example

In this example, we will add a WHERE clause that specifies to only return results where the “location” column in the department table equals ‘Bangalore’. This will filter the results to only show employees who belong to a department located in Bangalore, and departments that have no employees will not be returned in the results. 

Query: 

SELECT e.EmpID, e.Name, d.department_name, 
d.department_head, d.location 
FROM Emp e
LEFT JOIN department d ON e.department_id
 = d.department_id
WHERE d.location = 'Bangalore';

Output: 

SQL LEFT JOIN as Aliases Example

In this query, we’ll use aliases “e” for the Emp table and “d” for the department table. The SELECT statement references these aliases for each column, making the query easier to read and type. Aliases simplify code and improve readability, especially with long or complex table names.

Query:

SELECT e.EmpID, e.Name, d.department_name, 
d.department_head, d.location 
FROM Emp e
LEFT JOIN department d ON 
e.department_id = d.department_id;

Output:

• LEFT JOIN returns all records from the left table and matching records from the right table.
• NULL values are included for unmatched records on the right side.
• LEFT JOIN is used to combine data based on related columns.
• Aliases can simplify queries with long table names.
• LEFT JOIN with WHERE clause is used for filtering records.