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.
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.