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.
SQL Intersect operator
Intersect operator

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_noStudent_nameClassMarks
1AmitBE68
2RahulBE Second Year55
3RajuBE 43
Student Table

Second table

Table Name:Student_1

Roll_noStudent_nameClassMarks
1PradnyaBE52
2MohitBE47
3RajuBE43
Student_1

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_NoStudent_nameClassMarks
3RajuBE43
Intersect table

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 :

IntersectMinus
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 records3.Minus operator is preferable operator in Performance tuning.
Intersect vs Minus

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.

Leave a Reply

Your email address will not be published.