SQL Server Joins Interview Questions

Back to: Dot Net Interview Questions and Answers

SQL Server Joins Interview Questions and Answers

In this article, I am going to discuss the most frequently asked SQL Server Joins Interview Questions and Answers. Please read our previous article where we discussed the most frequently asked SQL Server Stored Procedure Interview Questions and Answers. As part of this article, we are going to discuss the following SQL Server Joins Interview Questions with answers.

1. What are Joins in SQL Server?
2. Explain the different types of Joins in SQL Server.
3. What are ANSI and NON-ANSI Joins in SQL Server?
4. Explain about EQUI Join in SQL Server
5. Explain about INNER JOIN in SQL Server
6. Explain about OUTER JOIN in SQL Server
7. What is the left outer join?
8. What is the right outer join?
9. What is the full outer join?
10. Explain about CROSS join in SQL Server
11. What is self-join in SQL Server?
12. Differences between UNION and UNION ALL in SQL Server
13. Difference between JOIN and UNION in SQL Server
14. What is a cross join? Explain with an example?
15. What is inner Join in SQL Server? Explain with an example?
16. What is the Left Outer Join in SQL Server? Explain with an example?
17. What is the Right Outer Join in SQL Server? Explain with an example?
18. What is Full Outer Join in SQL Server? Explain with an example?

What are Joins in SQL Server?

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. An SQL JOIN is used to combine rows from two or more tables based on a common field between them.

Explain the different types of Joins in SQL Server.

JOINS are classified into two types such as

1. ANSI format JOINS
2. NON-ANSI format JOINS

Again the ANSI format joins classified into three types such as

1. Inner join
2. Outer join
3. Cross join

Further, the outer join divided into three types they are as follows

1. Left outer join
2. Right outer join
3. Full outer join

NON-ANSI format JOINS are classified into four types such as

1. EQUI join
2. NON-EQUI join
3. SELF-join
4. Natural Join

What are the different types of joins available in SQL server?

There are 3 different types of joins available in SQL server, and they are

1. Cross Join 
2. Inner Join or Join 
3. Outer Join

What are ANSI and NON-ANSI Joins in SQL Server?

When we retrieve the data from multiple tables based on ‘ON’ keyword condition then it is called ANSI format joins whereas when we retrieve the information from multiple tables based on ‘WHERE’ keyword condition then it is called as NON-ANSI format joins.

The most important point we need to keep in mind is that when we implement the JOIN mechanism on the tables then the tables should contain a related column.

When we execute the JOINS on the tables then each record of a table will join with each record of another table. JOINS are using for retrieving the data purpose only but not executed purpose. When we get the result from multiple tables, the result is temporary only but not permanent storage of data.

What is the default JOIN in SQL Server?

The default join of joins is INNER JOIN.

What is the default join of outer join?

The default join of the outer join is a full outer join.

Explain about EQUI Join in SQL Server:

When we retrieve the data from the table based on equality condition then it is known as EQUI join. (Use equal “=” operator only). It is NON-ANSI format join. Hence when we make a query for join using an equality operator, then that join query comes under EQUI join.

Note:  Output depends on the table order, not on the query condition.

Explain about INNER JOIN in SQL Server.

Inner join returns only the matching rows between both the tables. Non-matching rows are eliminated. The inner join is used to retrieve matching data from both the tables. The join that displays only the rows that have a match in both the joined tables is known as an inner join.

Explain about OUTER JOIN in SQL Server.

The OUTER JOIN is an extension of INNER JOIN or EQUI JOIN. In an inner join, the user will get matching data only from the tables i.e. the user will lose un-matching data from the tables. So to overcome the above problem we use the outer join mechanism.

By using the outer join we can retrieve matching data and also un-matching data from the tables. Outer join again classified into three types.

1. Left outer join
2. Right outer join
3. Full outer join

What is the left outer join?

It retrieves matching data from both the tables and also un-matching data from the left side table only. Un-matching data take null value in this case.

What is the right outer join?

It retrieves matching data from both the tables and also un-matching data from the right side table.

What is the full outer join?

It retrieves matching data and also un-matching data from both tables at a time.

Explain about CROSS join in SQL Server.

If two or more tables are combined with each other without any condition we call it a Cartesian or cross join. When we join the records without any condition is known as CROSS JOIN. In cross join, each record of a table is joins with each record of another table. Cross Join should not have an ON clause.

A cross joins that produces the Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of rows in the first table multiplied by the number of rows in the second table like this.

Explain about NON-EQUI JOIN in SQL Server.

When we retrieve the information from the tables with any condition except equality condition then it is known as NON-EQUI JOIN. We may use <, >, <=,>=, <!,>!, AND, OR, NOT, etc except “=” operator.

What is self-join in SQL Server?

Joining a table by itself is called a self-join. When we have some relation between the columns within the same table then we use the self-join mechanism. When we implement a self-join mechanism we should create the alias for the table. We can create any number of aliases for a single table. Self-join is not a different kind of join. It can be classified of any type of join

1. Inner Join
2. Outer (Left, Right, full) join
3. Cross Join

Self-join is just like any other join except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers of that employee. So to find out the managers of all the employees, we need a self-join. 

Differences between UNION and UNION ALL in SQL Server:

Both UNION and UNION ALL are used to combines the result-set of two or more select queries into a single result-set. The difference between these two is UNION removes duplicate rows whereas UNION ALL does not. When use UNION, to remove the duplicate rows, the SQL server has to do a distinct sort which is time-consuming. For this reason, the UNION ALL is much faster than UNION. 

If we want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L. For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be the same.

If we want to sort the results of UNION or UNION ALL the ORDER BY clause should be used on the last SELECT statement as shown below.

Difference between JOIN and UNION in SQL Server:

JOINS and UNIONS are two different things. However, this question is being asked very frequently now. UNION combines the result-set of two or more select queries into a single result-set whereas JOINS retrieves data from two or more tables based on logical relationships between the tables.

What is a cross join? Explain with an example?

Let us understand Cross 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

A cross join produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. A query involving a CROSS JOIN for the Candidate and Company Table is shown below.

SELECT  Cand.CandidateId,
  Cand.FullName,
  Cand.CompanyId, 
  Comp.CompanyId,Comp.CompanyName
FROM	Candidate Cand
CROSS JOIN Company Comp

If we run the above query, we produce the result set shown in the image below.

Key Points to remember about CROSS JOIN. 

A cross join produces the Cartesian product of the tables involved in the join. This means every row in the Left Table is joined to every row in the Right Table. The candidate is LEFT Table and Company is RIGHT Table. In our example, we have 28 total numbers of rows in the result set. 7 rows in the Candidate table multiplied by 4 rows in the Company Table. 

In real-time scenarios, we rarely use a CROSS JOIN. Most often we use either INNER JOIN or LEFT OUTER JOIN. The CROSS JOIN does not have an ON clause with Join’s conditions. All the other JOINS use the ON clause with a Join Condition. Using an ON clause on a CROSS JOIN would generate a syntax error.

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.

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.

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.

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.

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

Here, in this article, I try to explain most frequently asked SQL Server JOINS Interview Questions and Answers. I hope you enjoy this SQL Server JOINS Interview Questions and Answers article. I would like to have your feedback. Please post your feedback, question, or comments about this SQL Server JOINS Interview Questions and Answers article.