Joins Interview Questions and Answers in SQL Server (Part – 2)

Joins Interview Questions and Answers in SQL Server (Part – 2)

In this article, I am going to continue the discussion on most frequently asked Joins Interview Questions and Answers in SQL Server. Please read our previous article before proceeding to this article where we discussed the basics interview questions in SQL Server Joins. As part of this article, we are going to discuss the following Joins Interview Questions and answers in SQL Server.

  1. What is inner Join in SQL Server? Explain with an example?
  2. What is the Left Outer Join in SQL Server? Explain with an example?
  3. What is the Right Outer Join in SQL Server? Explain with an example?
  4. What is Full Outer Join in SQL Server? Explain with an example?
What is inner Join in SQL Server? Explain with an example?

Let us understand the Inner join with an example. Create 2 Tables Company and Candidate. We are going to use the following two tables through out this article. Please use the script below to create these tables and populate them. CompanyId column in Candidate Table is a foreign key referencing CompanyId in 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 we want to select all the rows from the LEFT table (In our example Candidate Table) that have a non-null foreign key value (CompanyId in Candidate Table is the foreign key) then we use INNER JOIN. A query involving an INNER JOIN for the Candidate and Company Table is shown below. 

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

If we run the above query the output will be as shown in the image below. If we look at the output, we only got 5 rows. We did not get the 2 rows that have a NULL value in the CompanyId column. So, an INNER JOIN would get all the rows from the LEFT table that has non-null foreign key value.

SQL Server JOIN Interview Questions and Answers

Instead of using the INNER JOIN keyword we can just use the JOIN keyword as shown below. JOIN or INNER JOIN means the same.

SELECT       Cand.CandidateId, 
             Cand.FullName, 
             Cand.CompanyId, 
             Comp.CompanyId, 
             Comp.CompanyName
FROM Candidate Cand
JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
What is the Left Outer Join in SQL Server? Explain with an example?

Let us understand Left join in SQL Server with an example. If we want to select all the rows from the LEFT table (In our example Candidate Table) including the rows that have a null foreign key value (CompanyId in Candidate Table is the foreign key ) then we use LEFT OUTER JOIN. A query involving a LEFT OUTER JOIN for the Candidate and Company Table is shown below.

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 run the above query the output will be as shown below. If we look at the output, we now got all 7 rows (all the rows from the Candidate Table) including the row that has a null value for the CompanyId column in the Candidate Table. So, the LEFT OUTER JOIN would get all the rows from the LEFT Table including the rows that have null foreign key value.

Joins Interview Questions and Answers in SQL Server

Instead of using the LEFT OUTER JOIN keyword we can just use the LEFT JOIN keyword as shown below. 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
What is the Right Outer Join in SQL Server? Explain with an example?

Let us understand Right Outer join in SQL Server with an example. If we want to select all the rows from the LEFT Table (In our example Candidate Table) that have non-null foreign key values plus all the rows from the RIGHT table (In our example Company Table) including the rows that are not referenced in the LEFT Table, then we use RIGHT OUTER JOIN. A query involving a RIGHT OUTER JOIN for the Candidate and Company Table is shown below.

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

If we run the above query the output will be as shown below. If we look at the output, we now got 6 rows. All the rows from the Candidate Table that has non-null foreign key value plus all the rows from the Company Table including the row that is not referenced in the Candidate Table.

SQL Server JOIN Interview Questions and Answers

Instead of using the RIGHT OUTER JOIN keyword we can just use the RIGHT JOIN keyword as shown below. RIGHT OUTER JOIN or RIGHT JOIN means the same.

SELECT     Cand.CandidateId, 
           Cand.FullName, 
           Cand.CompanyId, 
           Comp.CompanyId, 
           Comp.CompanyName
FROM Candidate Cand
RIGHT JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
What is Full Outer Join in SQL Server? Explain with an example?

Let us understand Full Outer join in SQL Server with an example. If we want to select all the rows from the LEFT Table (In our example Candidate Table) plus all the rows from the RIGHT table (In our example Company Table), then we use FULL OUTER JOIN. A query involving a FULL OUTER JOIN for the Candidate and Company Table is shown below.

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

If we run the above query the output will be as shown below. If you look at the output, we now got 8 rows. All the rows from the Candidate Table and all the rows from the Company Table.

Joins Interview Questions and Answers in SQL Server

Instead of using FULL OUTER JOIN keyword we can just use FULL JOIN keyword as shown below. FULL OUTER JOIN or FULL JOIN means the same.

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

In the next article, I am going to discuss the most frequently asked experienced SQL Server Interview Questions with answers. Here, in this article, I try to explain most frequently asked Joins Interview Questions and Answers in SQL Server. I hope this article will help you with your needs. 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 *