SQL Server - LEFT JOIN Query

The LEFT JOIN is a type of inner join where it returns all the records from the left table and matching records from the right table. Here, the left table is a table that comes to the left side or before the "LEFT JOIN" phrase in the query, and the right table refers to a table that comes at the right side or after the "LEFT JOIN" phrase. It returns NULL for all non-matching records from the right table.

In some databases, it is called LEFT OUTER JOIN.

Syntax:

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

In the above syntax, table1 is the left table and table2 is the right table.

For the demo purpose, we will use the following Employee and Department tables in all examples.

Consider the following left join query.

SELECT emp.empid, emp.FirstName, dept.DeptId, dept.Name FROM Employee emp LEFT JOIN Department dept ON emp.DeptId = dept.DeptId;

The above LEFT JOIN query joins the Employee table and Department table where Employee is the left table and Department is the right table. It retrieves all the records from the Employee table and matching records from the Department table where emp.DeptId = dept.DeptId. The above query will display the following result.

Notice that it only displayed the records from the Department table whose dept.DeptId matches with the emp.DeptId.

Now, let's change the left table and see how the result will be changed.

SELECT emp.empid, emp.FirstName, dept.DeptId, dept.Name FROM  Department dept LEFT JOIN Employee emp ON dept.DeptId = emp.DeptId;

Above, left table is Department and the right table is the Employee. This query will display the following result.

As you can see, it fetches all the records from the Department table and only matching records from the Employee table where dept.DeptId = emp.DeptId. It contains the 3, 'Sales' record from the Department table and NULL for Employee columns because there are no matching records in the Employee table whose DeptId is 3.