SQL Server Joins Interview Questions (Part-1)

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?
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.

SQL Server JOINS Interview Questions and Answers

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.

In the next article, I am going to continue the discussion of Joins Interview Questions with answers. Here, in this article, I try to explain most frequently asked SQL Server JOINS Interview Questions and Answers. 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 *