SQL Left Join

Join operation in SQL is used to combine multiple tables together into a single table.
If we use left join to combine two different tables, then we will get all the records from the left table. But we will get only those records from the right table, which have the corresponding key in the left table. Rest other records in the right table for which the common column value doesn't match with the common column value of the left table; then, it is displayed as NULL.
Let us take a look at the syntax of writing a query to perform the left join operation in SQL.

SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;

Now let us see take a deeper dive into the left join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.

Consider we have the following tables with the given data:

Table 1: employee

EmployeeID	Employee_Name	Employee_Salary
1	Arun Tiwari	50000
2	Sachin Rathi	64000
3	Harshal Pathak	48000
4	Arjun Kuwar	46000
5	Sarthak Gada	62000
6	Saurabh Sheik	53000
7	Shubham Singh	29000
8	Shivam Dixit	54000
9	Vicky Gujral	39000
10	Vijay Bose	28000

Table 2: department

DepartmentID	Department_Name	Employee_ID
1	Production	1
2	Sales	3
3	Marketing	4
4	Accounts	5
5	Development	7
6	HR	9
7	Sales	10

Table 3: Loan

LoanID	Branch	Amount
1	B1	15000
2	B2	10000
3	B3	20000
4	B4	100000
5	B5	150000
6	B6	50000
7	B7	35000
8	B8	85000

Table 4: Borrower

CustID	CustName	LoanID
1	Sonakshi Dixit	1
2	Shital Garg	4
3	Swara Joshi	5
4	Isha Deshmukh	2
5	Swati Bose	7
6	Asha Kapoor	10
7	Nandini Shah	9

Table 5: customer

Customer_ID	Name	Age	Salary
1	Aryan Jain	51	56000
2	Arohi Dixit	21	25000
3	Vineet Garg	24	31000
4	Ajeet Singh	23	32000
5	Ravi Rathod	23	42000
6	Paras Aggrawal	22	50000
7	Sonakshi Kapadiya	24	28000
8	Sonali Kapoor	28	82000

Table 6: orders

Order_ID	Order_Date	Cutomer_ID	Amount
1	2012-01-20	2	3000
2	2012-05-18	2	2000
3	2012-06-28	3	4000
4	2012-04-11	4	5000
5	2012-05-04	8	8000

Example 1:

Write a query to perform the left join operation considering the employee table as the left table and the department table as the right table.

Query:

mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e LEFT JOIN department d ON e.EmployeeID = d.Employee_ID;

We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the LEFT JOIN keyword to perform the left join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.

You will get the following output:

EmployeeID	Employee_Name	Employee_Salary	DepartmentID	Department_Name
1	Arun Tiwari	50000	1	Production
2	Sachin Rathi	64000	NULL	NULL
3	Harshal Pathak	48000	2	Sales
4	Arjun Kuwar	46000	3	Marketing
5	Sarthak Gada	62000	4	Accounts
6	Saurabh Sheik	53000	NULL	NULL
7	Shubham Singh	29000	5	Development
8	Shivam Dixit	54000	NULL	NULL
9	Vicky Gujral	39000	6	HR
10	Vijay Bose	28000	7	Sales

EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name are retrieved from employee and department tables. All the records from the employee table are retrieved. Only those records that have a corresponding EmployeeID in the employee table are retrieved from the department table. Rest other records in the department table for which an EmployeeID doesn't match with the EmployeeID of the employee table; then, it is displayed as NULL.

Example 2:

Write a query to perform the left join operation considering the loan table as the left table and the borrower table as the right table.

Query:

mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l LEFT JOIN Borrower b ON l.LoanID = b.LoanID;

We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the LEFT JOIN keyword to perform the left join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.

You will get the following output:

LoanID	Branch	Amount	CustID	CustName
1	B1	15000	1	Sonakshi Dixit
2	B2	10000	4	Isha Deshmukh
3	B3	20000	NULL	NULL
4	B4	100000	2	Shital Garg
5	B5	150000	3	Swara Joshi
6	B6	50000	NULL	NULL
7	B7	35000	5	Swati Bose
8	B8	85000	NULL	NULL

LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the loan table are retrieved. Only those records that have a corresponding LoanID in the loan table are retrieved from the borrower table. Rest other records in the borrower table for which a LoanID doesn't match with the LoanID of the loan table; then, it is displayed as NULL.

Example 3:

Write a query to perform the left join operation considering the customer table as the left table and the orders table as the right table.

Query:

mysql> SELECT c.Customer_ID, c.Name, c.Age, c.Salary, o.Order_ID, o.Order_Date, o.Amount FROM customer c LEFT JOIN orders o ON c.Customer_ID = o.Customer_ID;

We have used the SELECT command to retrieve Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount present in customer and orders table. Then we have used the LEFT JOIN keyword to perform left join operation on the customer and orders table where 'c' and 'o' are aliases. These two tables are joined on the column Customer_ID which is present in both the tables.

You will get the following output:

Customer_ID	Name	Age	Salary	Order_ID	Order_Date	Amount
1	Aryan Jain	51	56000	NULL	NULL	NULL
2	Arohi Dixit	21	25000	1	2012-01-20	3000
2	Arohi Dixit	21	25000	2	2012-05-18	2000
3	Vineet Garg	24	31000	3	2012-06-28	4000
4	Ajeet Singh	23	32000	4	2012-04-11	5000
5	Ravi Rathod	23	42000	NULL	NULL	NULL
6	Paras Aggrawal	22	50000	NULL	NULL	NULL
7	Sonakshi Kapadiya	24	28000	NULL	NULL	NULL
8	Sonali Kapoor	28	82000	5	2012-05-04	8000

Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount are retrieved from customer and orders tables. All the records from the customer table are retrieved. Only those records that have a corresponding Customer_ID in the customer table are retrieved from the orders table.

Next TopicSQL Right Join

← prev next →