Intersect Operation in SQL

Intersect Operation in SQL

Many times you have a requirement like you want data from two table which are in comman that means Data distinct Present in both the Tables.
It's same like Inner Join and Exist Predicate but has a advantage over that with Null Values explained Below.

For this I will create two tables

Two table Employee1 and Employee2

create table Employee1

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee1 values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee1

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

create table Employee2

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000'),('arun','9500'),('akash','10500')

select * from Employee2

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

6 arun        9000

7 akash      10500

Now we are Running the Intersection Query

select * from Employee1

intersect

select * from Employee2

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

It's same like Inner Join and Exist Clause with the Advantage over them is that if both table have same values with Some Null fields in the Columns the intersect will include that row in the Result which is not in Case with Inner Joins and Exists predicate they both filtered out Null Values.

In order to include nulls in your output with inner join and Exist Predicate you have to write extra logic for that.

To check intersect with Nulls I am inserting Rows same Rows in both tables

insert into Employee1 values(null,'5000')

insert into Employee1 values(null,'5000')

insert into Employee1 values(null,'5000')

--The Reason i have inserted the values 3 times to make both row similar as we have autoincreament column in both tables but employee 1 only has 5 records and employee 2 has 7 so we have to match Empid also to make both rows similar

insert into Employee2 values(null,'5000')

Now we are Running the Intersection query again

select * from Employee1

intersect

select * from Employee2

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

8 Null           5000

the query has included Null rows also check with inner join and exist they will exclude Nulls.