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.
- This is introduced in SQL Server 2005.
- The number of columns and order of columns should be the same.
- The data types must be the same or least compatible.
- 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 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?
The Union operator will return all unique rows from the left query and right query and union all operator will include duplicate also.
The intersect operator will retrieve all unique rows from the left and right queries.
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 columns which are unmatched of table-valued function is returned as null.