What is Left Join (or) Left Outer Join in SQL Server?

Introduction:

In this article, I will explain what is Left Join (or) Left Outer Join in SQL Server.

Description:

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. 

Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables returned in a full outer join.

SQL Server uses the following ISO keywords for outer joins specified in a FROM clause:

• LEFT OUTER JOIN (or) LEFT JOIN
• RIGHT OUTER JOIN (or) RIGHT JOIN
• FULL OUTER JOIN (or) FULL JOIN

LEFT OUTER JOIN or LEFT JOIN:

Step 1: Create Employee, Department tables. 

Step 2: Insert data into Employee, Department tables.

Get Employee data:

select  * 
from    Employee

Output:

Get Department data:

select  * 
from    Department

Output:

Syntax:

Select      *
From        Table1
left join   Table2
on          Table1.Col1 = Table2.Col1

Here you will get all rows from Table1 and matching rows from Table2 where Col1 of Table1 is equivalent to Col1 of Table2. Here *(star) means you will get all columns from both the tables.

Example:

Select      *
from        Employee E
left join   Department D
on          E.DepartmentID = D.DepartmentID

Output:

Here you will get all rows from the Employee table. And matching rows from the Department table.

Note: There are no rows with the department name Java and C++.