Back to: SQL Server Tutorial For Beginners and Professionals
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 SQL Server Inner Join with Examples. At the end of this article, you will understand the following pointers in detail.
- What is Left Outer Join?
- How to implement Left Outer Join in SQL Server?
- When do we need to use Left JOIN in SQL Server?
- Example to Understand SQL Server Left Outer Join
- How to retrieve only the non-matching rows from the left table in SQL Server?
- Joining three Tables in SQL Server using Left Join
What is Left Outer Join in SQL Server?
The Left Outer Join in SQL Server is used to retrieve the matching records from both the tables as well as the non-matching records from the left side table involved in the JOIN. 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 the pictorial representation of the Left Outer Join.
The question that should come to your mind is which is the left table and which is the right table? The answer is, the table which is mentioned to the left of the LEFT OUTER JOIN keyword is the left table, and the table which is mentioned to the right of the LEFT OUTER JOIN keyword is the right table.
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 in SQL Server. Here, you can use either the Left Outer Join or the Left Join keywords to perform the Left Outer Join operation. If you use only the Left JOIN keyword, then also it is going to perform the Left Outer Join operation.
When do we need to use Left JOIN or Left Outer 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 the above definition, we can say that if you want to retrieve all the matching records from both the tables involved in the join as well as the non-matching records from the left side table then we need to use Left Join or Left Outer Join in SQL Server.
Example to Understand Left Outer Join in SQL Server.
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.
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
Now our requirement is to write a query, to retrieve the CandidateId, FullName, CompanyId, and CompanyName from Company and Candidate tables. The output of the query should be as shown below.
Following is the SQL 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 you look at the output, here, 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.
Instead of using the Left Outer Join keyword, we 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. Now, let us proceed and understand some tricky questions which may be asked in SQL Server interviews.
How to retrieve only the non-matching rows from the left table in SQL Server?
Here, 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.
As we already discussed, the Left Outer Join is used to fetch all the matching records from both the tables as well as all the non-matching records from the left-hand side table. In this case, the non-matching records will take NULL value. So, to get the above output what we need to do is, we need to perform Left Outer Join, and then in the where condition we need to filter out the records which have NULL values. The following Left OuterJoin 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 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.
Please use the following SQL Script to create and populate the Address table with the required sample 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.
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.
Note: You need to use LEFT OUTER JOIN when you want to retrieve all the matching records from both the tables involved in the JOIN as well as non-matching records from the left-hand side table.
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.