SQL LEFT JOIN

In this tutorial, we will learn how to use LEFT JOIN or LEFT OUTER JOIN keyword and join two or more tables using SQL.

SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword returns all the records from the left table, and return matched records from the right table. If The result is NULL from the right side table if there is no match found.

SQL LEFT JOIN or LEFT OUTER JOIN both are the same.

Note:- In some databases, LEFT JOIN is called LEFT OUTER JOIN.

Syntax - LEFT JOIN or LEFT OUTER JOIN

SELECT column1, column2,...columnN
FROM tableName1
LEFT JOIN tableName2 ON tableName1.column1 = tableName2.column1;

Example:

Let us consider three tables "Employee", "Department" and "Designation" for records

Table Name : Employee

ID	EmpName	City	Country	Gender	Salary	DeptID	DesigID
1	Shankar	Delhi	India	male	25000	1	6
2	Sourabh	Delhi	India	male	30000	1	2
3	Ranvijay	Mumbai	India	male	15000	2	3
4	Kapil	Noida	India	male	25000	3	4
5	Shalini	Jaipur	India	female	18000	2	NULL
6	Rakesh	Faridabad	India	male	23000	1	2
7	Akshay	Mumbai	India	male	21000	2	4
8	Sarah	New York	US	female	76000	3	1
9	Rocky	Noida	India	male	28000	NULL	6

Table: Department

DeptID	DepartmentName
1	IT
2	Finance
3	HR

Table: Designation

DesigID	DesignationName
1	GM
2	Manager
3	CEO
4	Developer
5	Team Leader
6	Executive

Example of LEFT JOIN Keyword

For selecting an employee name, department name and designation name, a query will be:

Example - LEFT JOIN

SELECT EMP.EmpName,Dept.DepartmentName,Desg.DesignationName 
FROM Employee EMP 
LEFT JOIN Department Dept ON EMP.DeptId=Dept.DeptID 
LEFT JOIN Designation Desg ON EMP.DesignationId=Desg.DesigID

Output

EmpName	DeptartmentName	DesignationName
Shankar	IT	Executive
Sourabh	IT	CEO
Ranvijay	Finance	Manager
Kapil	HR	Developer
Shalini	Finance	NULL
Rakesh	IT	Manager
Akshay	Finance	Developer
Sarah	HR	GM
Rocky	NULL	Executive