Learn To Use Union, Intersect, And Except Clauses

n this article, we will understand Union, Intersect, and Except clause in SQL Server. Also, we will see the difference between Intersect and Union in SQL Server. Also, the difference between Join with Intersect by Sagar Jaybhay

Intersect operator

 

The intersect operator retrieves the common records between the left and right queries of the intersect operator.

1. This is introduced in SQL Server 2005.
2. The number of columns and order of columns should be the same.
3. The data types must be the same or least compatible.
4. It filters duplicate records and selects only distinct records that are common in the left and right queries. But if you use inner join then it will not filter distinct records.

To use inner join sothat it behaves like intersect you need to use distinct records, 

select * from Employee where EmpID between 1 and 100
intersect
select * from Employee where EmpID between 1 and 10;

 

In this we also get the result using inner join and below is a result, 

select * from Employee as e
inner join (select * from Employee where EmpID between 1 and 10) as a on e.EmpID=a.EmpID

 

In an inner join, it treats two Null values as different and if you join two tables on that column that has null values then inner join will not include those null value records where intersect treats null as  thesame and returns matching records.

 

What is the difference between Union, Intersect, and Except operator in SQL Server?

 

Union

 

The Union operator will return all unique rows from the left query and right query and union all operator will include duplicate also.

 

Intersect

 

The intersect operator will retrieve all unique rows from the left and right queries.

 

Except

 

The Except operator will retrieve all the unique rows from the left query that are not present in the right query result set.

 

Cross Apply and Outer Apply In SQL Server

 

The Apply Operator is introduced in SQL Server 2005 and it is basically used to join a table and table-valued function.

 

The table-valued function on the right-hand side gets called for each row in the left-hand side table.

 

The cross apply will return only matching rows and it is similar to an inner join.

 

Outer apply will return matching rows as well as non-matching rows and is similar to left join, and the unmatched columns  of table-valued function are returned as null.