SQL Intersect operator Examples | How to use SQL Intersect Operator?

In my previous articles I have given the detailed idea about union and union all operator with real life example. In this article i would like to throw light on SQL Intersect Operator with its real life industry examples. In many interview questions they are asking about intersection operator. There are so many operators in SQL which are useful. I would like to give you bullet points about SQL intersect operator with real time industry examples.

What you will find in this article?

1. What are multiple types of Set operators in SQL?
2. SQL Intersect Operator with real industry examples
3. Some Important Interview Questions related to SQL Intersect Operator.

What are multiple types of Set operators in SQL?

The set operators are used to perform multiple set operations on tables in database. There are following 4 basic set operators used in SQL :

1. Union
2. Union All
3. Intersect
4. Minus

The above set operators are used to perform multiple set operations on tables. You may get so many questions in interview about these set operators.

SQL Intersect Operator with real industry examples :

In this section we will focus on SQL intersect operator and how it is used in real life industry examples in detail. We will see multiple real life examples of intersection in SQL.

1. SQL Intersect Operator combines the result of two or more tables where the column names and datatypes of the multiple tables needs to be similar.
2. The Intersect operator will fetch the exact same rows from multiple tables.
3. Just make sure that Every select statement within intersect must have same number of columns.
4. The columns must have similar datatype and we require to follow order as well.

5.In above diagram if we see there are two sets : One set contains 1,2 and other set contains 1,2,3,4 as values. If we require to combine the dataset  and fetch the exactly identical records or similar records from both datasets we need to use intersect operator. It will shows only similar records.

6.Intersect operator only shows or fetches same records from both the tables.

7.MySQL does not supports the intersect operator.

7. Syntax of Intersect operator:

Select column1…column n from table1;

Intersect

Select column1…column n from table2;

Real Life Example of Intersect operator :

If you want to fetch the common records from Student and Student_1 table.

Student :

Roll_no	Student_name	Class	Marks
1	Amit	BE	68
2	Rahul	BE Second Year	55
3	Raju	BE	43

Second table

Table Name:Student_1

Roll_no	Student_name	Class	Marks
1	Pradnya	BE	52
2	Mohit	BE	47
3	Raju	BE	43

Query:

Select Roll_no,Student_name,Class,Marks from Student;

Intersect

Select Roll_no,Student_name,Class,Marks from Student_1;

Output:

The above Query should fetch only the common records:

Roll_No	Student_name	Class	Marks
3	Raju	BE	43

Some important interview questions for SQL Intersect Operator :

In this section we will see the SQL intersect operator interview questions :

Question 1 : What is difference between Intersect and Minus operator?

Answer :

Intersect	Minus
1.Intersect Set operator is used to fetch the common records from 2 different tables .	1.Minus Operator is used to fetch the records from first table which eliminates common records.
2.Syntax:Select col1,col2…from table1;IntersectSelect col1,col2…from table2;	2.Syntax:Select col1,col2…from table1;MinusSelect col1,col2…from table2;
3.For Performance tuning Intersect operator is not preferable as it takes time to fetch duplicate records	3.Minus operator is preferable operator in Performance tuning.

Question 2 : Which is faster operation – Intersect or Join?

Answer :

The Join operator more faster than intersect operator.

Question 3 : How to eliminate the intersect operator?

Answer :

You can replace the intersect operator with inner join. The resultset is same for intersect operator and inner join but inner join is quite faster than intersect operator.

Example :

 Select a.Roll_no,a.Student_name,a.Class,a.Marks from Student a,Student_1 b where a.roll_no=b.roll_no

I hope you like this article on intersect operator in SQL. If you like this article or if you have any issues kindly comment on comments section.