SQL Left Join

The LEFT JOIN keyword in SQL returns all matching records(or rows) and the records(or rows) that are present in the left table but not in the right table. 

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

Syntax 

SELECT column_name(s) 

FROM tableA 

LEFT JOIN tableB ON tableA.column_name = tableB.column_name;

Example 

In this example, we will 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:

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 Join as Aliases

Now in this query, we will use the aliases “e” for the Emp table and “d” for the department table. Then the SELECT statement then references these aliases for each of the columns being returned, making our query easier to read and type out. Aliases are especially useful when working with tables that have long or complicated names, as they can help simplify the code and make it easier to understand.

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 Join with WHERE Clause

Now in this query, 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: