Basics: What is left join and how to implement it?

Question:
What is left join and how to implement it?

Answer:
When you wish to have all the data selected from first table and all respective matching records from second table then we use Left Join aka Left Outer Join.

Why is it required?
Many times we have parent child tables in our database. Often, we need to display all the child records along with the parent entries. e.g. we have Departments  and Employees, Classes and Students, Orders and OrderItems, Invoices and InvoiceItems, Account Heads and Account Sub-Heads, Product Groups and Products,... In all these examples first item is Parent and second one is child to it. We may need to display All Employees along with their Departments or all Products along with their Product Groups. In such cases Left Join proves efficient method to gather data.

How it works?
Consider the following tables

Employee Table:

EmployeeId	Name	DepartmentId
1	Vinod	1
2	Sandeep	2
3	Pradyumna	2
4	Amol	3

Department Table:

DepartmentId	Name
1	Sales
2	Production
3	Purchase

Now what we need is to show All employees from Employee table and respective Department Names from Department table in a single result set.

Well, here is a statement:

SELECT e.EmployeeId, e.Name, d.Name AS DepartmentName FROM Employee e LEFT JOIN Department d ON e.DepartmentId=d.DepartmentId

Here is a result:

EmployeeId	Name	DepartmentName
1	Vinod	Sales
2	Sandeep	Production
3	Pradyumna	Production
4	Amol	Purchase

In above example, you will notice DepartmentName is alias created for Name field from Department table, also, d and e as aliases for Department and Employee tables respectively.

You can join many tables using left join simply by replicating Left join ... On ...

Consider one more table Designation same as department. You can join it by adding one more left join.

e.g

SELECT e.EmployeeId, e.Name, d.Name AS DepartmentName, ds.Name as Designation FROM Employee e
LEFT JOIN Department d ON e.DepartmentId=d.DepartmentId
LEFT JOIN Designation ds ON e.DesignationId=ds.DesignationId