The SQL Left Join returns all the rows or records present in the Left table and matching rows from the right table. The visual representation of the SQL Server Left Outer joins is
From the above image, the Sql Server Left Outer join displays all the records present in Table 1 and matching records from Table 2. All the Unmatched rows from the right table will fill with NULL Values.
SQL LEFT JOIN Syntax
The syntax of the SQL Server Left Join is
-- SQL Server LEFT JOIN Syntax SELECT Table1.Column(s), Table2.Column(s), FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Common_Column = Table2.Common_Column --OR We can Simply Write it as SELECT Table1. Column(s), Table2. Column(s), FROM Table1 LEFT JOIN Table2 ON Table1.Common_Column = Table2.Common_Column
The Left Outer join can also call as Left Join. So it is optional to use the Outer Keyword. For this Sql Server Left Join example, We use the Employee Table is:
Department Table
SQL Left Join Select All Columns
The following Left Outer Join Query will display all the columns present in Employees, and matching records from Department tables
-- SQL Server LEFT JOIN Example SELECT * FROM [Employee] LEFT OUTER JOIN [Department] ON [Employee].[DepartID] = [Department].[id]
Left Join avoid Outer keyword
Let me remove the Outer keyword, which is optional and work will LEFT JOIN
-- SQL Server LEFT JOIN Example SELECT * FROM [Employee] LEFT JOIN [Department] ON [Employee].[DepartID] = [Department].[id]
If you observe the above screenshot, We have 15 records in the Employee table. And the Left Outer Join is displaying 15 records, but for [DepartID], id, [Department Name], it is displaying NULL Values for ID numbers 10, 11, 14, and 15. It is because the Department Id for them in the Employee table is NULLS, so there are no matching records in the right table.
The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns. So, please avoid SELECT * Statements in Left Join
SQL Left Join Select Few Columns
Please place the required columns after the SELECT Statement to avoid unwanted SQL Server columns in Joins
-- SQL Server LEFT JOIN Example SELECT [FirstName] ,[LastName] ,[DepartmentName] FROM [Employee] LEFT JOIN [Department] ON [Employee].[DepartID] = [Department].[id]
The above query work as long as the column names from both tables are different like above. If we have the identical Column names in both the tables, left join will throw an error. For instance, we are using the above Left Join query. But, we added id from department table as an additional column.
-- SQL Server LEFT JOIN Example SELECT [FirstName] ,[LastName] ,id ,[DepartmentName] FROM [Employee] LEFT OUTER JOIN [Department] ON [Employee].[DepartID] = [Department].[id]
As you can see, the left join is throwing an error: Ambiguous column name id. It is because the id column is present in both the tables. And SQL Server doesn’t understand which column you are requesting it to retrieve.
To resolve this sort of issue, forever use the table name before the column name. The following left Outer Join query is using the ALIAS table name before the column names. By this method, we can inform the SQL Server that we are looking for id column relating to the department table. We can rewrite the above query as:
-- SQL Server LEFT JOIN Example SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.id ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp LEFT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id]
Left Join Where Clause
The SQL Server Left Join also permits us to use Where Clause to limit the number of rows returned by the Left Outer Join. Here, we use WHERE Clause along with the Left Outer Join.
-- SQL Server LEFT JOIN Example SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp LEFT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] WHERE Dept.[DepartmentName] IS NOT NULL
Left Join Order By Clause
The SQL Server Left Outer Join allows us to use Order By Clause in Left Outer Join to rearrange the order of the records.
-- SQL Server LEFT JOIN Example SELECT Emp.[FirstName] AS [First Name] ,Emp.[LastName] AS [Last Name] ,Dept.[DepartmentName] AS [Department Name] FROM [Employee] AS Emp LEFT JOIN [Department] AS Dept ON Emp.[DepartID] = Dept.[id] ORDER BY [DepartmentName] ASC