SQL LEFT JOIN

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 right table records will contain NULL values.

LEFT JOIN in SQL

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 a certain row is present in the left table but not in the right table, the result will include this row, but with a NULL value in each column from the right table. If a record from the right table is not on the left, it will not be included in the result.

SQL LEFT JOIN Venn Diagram

This VENN diagram shows how a LEFT JOIN works in SQL.

LEFT JOIN

Syntax

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:

Employee Table

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:

Department Table

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:

LEFT JOIN in SQL Example 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 with WHERE Clause Example

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:

SQL LEFT JOIN as Aliases Example Output

 Important Points About SQL LEFT JOIN

• LEFT JOIN in SQL 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.

D

disha55handa

Follow

Improve