SQL Left Join
The LEFT JOIN keyword in SQL returns the 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 in the left, it will not be included in the result.
The syntax for a LEFT JOIN is :
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
SELECT column_name(s) FROM tableA LEFT JOIN tableB ON tableA.column_name = tableB.column_name;
SQL LEFT JOIN EXAMPLE :
In this example we will consider two tables Employee containing details of the employees working in the particular department the and department table containing the details of the department
emp_no emp_name age salary dept_no E1 Varun Singhal 27 30,000 D1 E2 Amrita Aggarwal 28 25,000 D2 E3 Ravi Anand 30 34,000 D1 E4 Nitin Saini 34 54,000 [NULL] E5 Muskan Garg 35 65,000 [NULL]
dept_no dept_name location D1 IT Delhi D2 HR Hyderabad D3 FINANCE Rajasthan
To perform left- join on these two tables we will use the following SQL query :
select emp_no ,emp_name , age, salary ,dept_name, location from employee left join department on empolyee.dept_no=department.dept_no;
The output that we will get is as follows :-
emp_no emp_name age salary dept_name location E1 Varun Singhal 27 30,000 IT Delhi E3 Ravi Anand 30 34,000 IT Delh E2 Amrita Singhal 28 25,000 HR Hyderabad E4 Nitin Saini 34 54,000 [NULL] [NULL] E5 Muskan Garg 35 65,000 [NULL] [NULL]
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 does not work in a particular department, i.e, having dept no values as [NULL], contains [NULL] values of dept name and location after the left join.