Learn to use Union, Intersect, and Except Clauses

In 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

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.
5. To use inner join behave 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 2 Null values are different and if you join 2 tables on that column on which column has null values then inner join will not include that null value records where intersect treat null as same and return 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.

Complete Article: — https://sagarjaybhay.com/union-intersect-and-except-clauses/

Originally published at https://sagarjaybhay.com on May 6, 2020.