SQL Server Left Outer Join

SQL Server Left Outer Join with Real-Time Example

In this article, I am going to discuss the SQL Server Left Outer Join with a real-time example. Please read our previous article where we discussed the SQL Server Inner Join with an example. The SQL Server Left Outer Join is used to retrieve the matching records from both the tables as well as the non-matching records from the left side table. In that case, the un-matching data will take null value. If this is not clear at the moment then don’t worry we will see it with an example.

SQL Server Left Outer Join

Let us understand SQL Server Left Outer Join with an example.

In this demo, we are going to use the following Company and Candidate tables.

SQL Server Left Outer Join

Please use the below SQL script to create the Company and Candidate tables and populate with some test data. Please note that the CompanyId column in Candidate Table is a foreign key referencing to the CompanyId column of the Company Table.

CREATE TABLE Company
(
    CompanyId TinyInt Identity Primary Key,
    CompanyName Nvarchar(50) NULL
)
GO

INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO
CREATE TABLE Candidate
(
    CandidateId tinyint identity primary key,
    FullName nvarchar(50) NULL,
    CompanyId tinyint REFERENCES Company(CompanyId)
)
GO

INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO

If you want to select all the records from the Left side table in our case it is the Candidate Table including the records that have a null foreign key value then you need to use the SQL Server Left Outer Join.

Example:

The following is an example of SQL Server Left Outer Join which will join the Candidate and Company Table.

SELECT Cand.CandidateId, Cand.FullName, Cand.CompanyId, Comp.CompanyId, Comp.CompanyName
FROM Candidate Cand
LEFT OUTER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId

When you run the above left outer join query it will give you the output as shown in the below image.

Left Outer Join in SQL Server

If we look at the above output, we got all 7 rows (i.e. all the rows from the Candidate Table) including the row that has a null value for the CompanyId column. So this proofs that the SQL Server Left Outer Join will retrieve all the rows from the Left-hand side Table including the rows that have a null foreign key value.

Note:

Instead of using Left Outer Join keyword, you can also use the Left Join keyword. This will also work as expected as Left Outer Join. That means the Left Outer Join or Left Join means the same. 

SELECT Cand.CandidateId, Cand.FullName, Cand.CompanyId, Comp.CompanyId, Comp.CompanyName
FROM Candidate Cand
LEFT JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId

In short, we can say that Left Outer Join will return all the matching records from both the tables involved in the join as well as all the non-matching records from the left-hand side table. In the next article, I will discuss Right Outer Join in SQL Server with one real-time example.

SUMMARY

In this article, I try to explain the SQL Server Left Outer Join with one example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *