SQL Server JOINS Interview Questions and Answers (Part – 1)
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 JOINS Interview Questions and Answers.
What is JOINS?
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
- ANSI format JOINS
- NON-ANSI format JOINS
Again the ANSI format joins classified into three types such as
- Inner join
- Outer join
- Cross join
Further, the outer join divided into three types they are as follows
- Left outer join
- Right outer join
- Full outer join
NON-ANSI format JOINS are classified into four types such as
- EQUI join
- NON-EQUI join
- 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
- Cross Join
- Inner Join or Join
- Outer Join
What are ANSI and NON-ANSI Joins?
When we retrieve the data from multiple tables based on ‘ON’ keyword condition then it is called as 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 points 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 full outer join.
Explain about EQUI Join.
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 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.
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.
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 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.
- Left outer join
- Right outer join
- 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?
If two or more tables are combined with each other without any condition we call it as 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 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 the rows in the first table multiplied by the number of rows in the second table like this.
Explain about NON-EQUI JOIN.
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?
Joining a table by itself is called as 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
- Inner Join
- Outer (Left, right, full) join
- 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:
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, SQL server has to do a distinct sort which is time-consuming. For this reason the UNION ALL is much faster than UNION.
Note1: If we want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.
Note2: 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:
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 retrieve 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 CROSS JOIN. Most often we use either INNER JOIN or LEFT OUTER JOIN.
CROSS JOIN does not have an ON clause with Join conditions. All the other JOINS use ON clause with a Join Condition.
Using an ON clause on a CROSS JOIN would generate a syntax error.
Please Find below articles to know more about SQL Server JOINS.
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 need. I would like to have your feedback. Please post your feedback, question, or comments about this article.