SQL Server JOIN Interview Questions  Part -2

SQL Server JOIN Interview Questions and Answers (Part – 2)

Joins in SQL Server are used to retrieve data from 2 more related tables. In general, tables are related to each other using foreign key constraints. In this article, we will discuss most frequently asked SQL Server JOIN Interview Questions and Answers.

What is inner Join? Explain with an Example?

Inner Join and left join are the most commonly used joins in real time projects. We will talk about left join in a later article. Now, let us understand Inner join with an example.

Create 2 Tables Company and Candidate. 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 which have the 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 INNER JOIN keyword we can just use 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? Explain with an example?

Inner Join and left join are the most commonly used joins in real time projects.  Now, let us understand Left join with an example.

Create 2 Tables Company and Candidate. 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) 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, LEFT OUTER JOIN would get all the rows from the LEFT Table including the rows that have null foreign key value.

SQL Server JOIN Interview Questions and Answers

Instead of using LEFT OUTER JOIN keyword we can just use 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? Explain with an example?

Inner Join and left join are the most commonly used joins in real time projects. 

Now, let us understand Right Outer join with an example.

Create 2 Tables Company and Candidate. 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 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 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? Explain with an Example?

Inner Join and left join are the most commonly used joins in real time projects. It is very important that we understand the basics of joins before reading this article.

Now, let us understand Full Outer join with an example.

Create 2 Tables Company and Candidate. 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) 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.

https://3.bp.blogspot.com/-6WESV5hyAn8/Wso3MmOXLQI/AAAAAAAAAz8/YYZ0UwyNCR8aJK-QHVfXFWFL8g9YW-k5gCLcBGAs/s640/S5.PNG

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

Please find below articles for more details about SQL Server JOIN

SQL Server JOIN

Cross JOIN in SQL Server

Inner JOIN in SQL Server

Left Outer Join in SQL Server

Right Outer Join in SQL Server

Full Outer JOIN in SQL Server

Self JOIN in SQL Server

SUMMARY

In this article, I try to explain most frequently asked SQL Server JOIN Interview Questions and Answers. 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 *