Left Outer Join in SQL Server

Left Outer Join in SQL Server with Examples

In this article, I am going to discuss the Left Outer Join in SQL Server with Examples. Please read our previous article where we discussed the SQL Server Inner Join with an example. At the end of this article, you will understand the following pointers in detail.

  1. What is Left Outer Join?
  2. How to implement Left Outer Join in SQL Server?
  3. When do we need to use Left JOIN in SQL Server?
  4. Example to Understand SQL Server Left Outer Join
  5. How to retrieve only the non-matching rows from the left table in SQL Server?
  6. Joining three Tables in SQL Server using Left Join
What is Left Outer Join?

The 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 the null value. If this is not clear at the moment then don’t worry we will see it with an example. The following diagram shows Left Outer Join in SQL Server.

Left Outer Join in SQL Server

How to implement Left Outer Join in SQL Server?

Please have a look at the following image which shows the syntax of Left Outer Join. Here, you can use either Left Outer Join or Left JOIN keyword to perform Left Join. If you use only the Left JOIN keyword, then it is also going to perform Left Outer Join.

Left Outer Join Syntax in SQL Server

When do we need to use Left JOIN in SQL Server?

If you want to select all the records from the Left side table including the records that have a null foreign key value then you need to use the SQL Server Left Outer Join. To simplify this we can say that if you want to retrieve all the matching rows from both the tables as well as the non-matching rows from the left side table then you need to use Left Join in SQL Server. 

Example to Understand SQL Server Left Outer Join.

Let us understand how to implement Left Outer Join in SQL Server with Examples. To understand Left Outer Join in SQL Server, 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 them with some test data. Please note that the CompanyId column in the Candidate Table is a foreign key referencing 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

Write a query, to retrieve CandidateId, FullName, CompanyId, and CompanyName from Company and Candidate tables. The output of the query should be as shown below.

Left Outer Join Example in SQL Server

Following is the Query which is an example of Left Outer Join that is joining the Company and Candidate tables and will give the results shown in the above image.

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

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 the 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. Following is the SQL Query that uses Left Join Keyword to join Candidate and Company tables.

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. 

How to retrieve only the non-matching rows from the left table in SQL Server?

What we want is, we want to retrieve only the non-matching records from the left table. For example, we want the following result sets when we join the Candidate and Company tables. If you further notice, here we want to retrieve those rows from the Candidate table whose does not have any reference in the Company table.

How to retrieve only the non-matching rows from the left table in SQL Server.

The following Inner Join Query with the where clause does 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
WHERE Comp.CompanyId IS NULL;
Joining three Tables in SQL Server using Left Join:

Now we will see how to Inner Join three tables in SQL Server. Already we have the Company and Candidate tables. Let us introduce the third table i.e. the Address table. So, we are going to use the following Address table along with the Company and Candidate tables to perform joining operations between three tables in SQL Server.

Joining 3 Tables in SQL Server

Please use the following SQL Script to create and populate the Address table with the required test data.

CREATE TABLE Address
(
    AddressId INT IDENTITY PRIMARY KEY,
    CandidateId tinyint REFERENCES Company(CompanyId),
    Country VARCHAR(50),
    State VARCHAR(50),
    City VARCHAR(50),
)
GO

INSERT INTO Address Values (1, 'India', 'Odisha', 'BBSR');
INSERT INTO Address Values (2, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address Values (3, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address Values (4, 'India', 'Odisha', 'Cuttack');
GO

The following is the syntax to join three tables in SQL Server.

Joining 3 Tables in SQL Server using Left Outer Join

Example: Left Joining Candidate, Company, and Address tables

The following Left Join SQL Query Joining Candidate, Company, and Address tables.

SELECT	Cand.CandidateId, 
        Cand.FullName, 
        Cand.CompanyId, 
        Comp.CompanyName,
        Addr.Country,
        Addr.State,
        Addr.City
FROM	Candidate Cand
LEFT JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
LEFT JOIN Address Addr
ON Addr.CandidateId = Cand.CandidateId;

When you execute the above SQL Query, you will get the following output. 

Left Join in SQL Server with Examples

In the next article, I am going to discuss Right Outer Join in SQL Server with one real-time example. Here, in this article, I try to explain the SQL Server Left Outer Join with Examples. 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 *