SQL Server - Intersect

SQL Server - Intersect

In simpler words INTERSECT operator returns only values that match within both data sets.

Example 1 of Intersect: Using two tables

Table A

A
1
2
3
4

Table B

B
3
4
5
6

Select * from A
Intersect
Select * from B

Result:

A
3
4

Example 2 of Intersect: Using individual columns

Table Mobiles

MobileID	MobileName	Company
1	Nokia 100	MS
2	Nokia 120	MS
3	Nokia 130	MS
4	N72	Nokia
5	P21	Panasonic
6	M69	Micromax

Table Mobiles_New

MobileID	MobileName	Company
1	Microsoft 100	Microsoft
2	Microsoft 120	Microsoft
3	Microsoft 130	Microsoft
4	Nokia 72	Microsoft
7	iPhone	Apple

Select * from Mobiles
Intersect
Select * from Mobiles_New

Result:

MobileID	MobileName	Company

No two rows are same in the above two tables (Mobiles and Mobiles_New), hence no results, as seen above. Now lets take only first column in both the tables and use intersect.

Select a.MobileID from Mobiles a
Intersect
Select b.MobileID from Mobiles_New b

Result:

MobileID
1
2
3
4

Example 3 of Intersect: Using three tables

Table A

A
1
2
3
4

Table B

B
3
4
5
6

Table C

C
4
5
6
7

Select * from A
Intersect
Select * from B
Intersect
Select * from C

Result:

A
4