T-SQL EXCEPT and INTERSECT
Both T-SQL EXCEPT and INTERSECT are set based operators that combine multiple query results back in the same result set. EXCEPT returns the records from the query on the left that are not found in the right query. INTERSECT returns the distinct rows that are in both the right and left queries.
When using EXCEPT or INTERSECT there are 2 base rules. First the number and order of the columns must be the same in all statements in the queries. Second the data types must be compatible.
The following queries progress through a sequence to show how these operators work. I have used the AdventureWorks2014 database as my sample queries. For these examples I use the Product and SalesOrderDetail tables.
–GET DISTINCT PRODUCTS
SELECT distinct ProductID
FROM [AdventureWorks2014].[Production].Product
–504 rows
–GET TOTAL SalesOrderDetail records
select count(*)
from [Sales].[SalesOrderDetail]
–121317 total rows
–GET TOTAL distinct ProductID from SalesOrderDetail table
select distinct ProductID
from [Sales].[SalesOrderDetail]
–266 rows
Examples of EXCEPT and INTERSECT
In this example I want to see all the ProductIDs in the product table that do not have a record in the SalesOrderDetail table. This would be useful with working with data validation or master data loading processes.
EXCEPT Example
SELECT ProductID
FROM [AdventureWorks2014].[Production].Product
EXCEPT
SELECT Productid
FROM [Sales].[SalesOrderDetail]
— 238 rows using EXCEPT
To verify the result set and see that the returned Product IDs are not in the SalesOrderDetail take a Product ID returned (I used 365) and run against the SalesOrderDetail.
select * from Sales.SalesOrderDetail
where ProductID = 365
— no row returned
INTERSECT Example
In this example I want to see all the ProductIDs in the product table that are also in the SalesOrderDetail table.
SELECT ProductID
FROM [AdventureWorks2014].[Production].Product
INTERSECT
SELECT productid
FROM [Sales].[SalesOrderDetail]
— 266 rows using intersect
To verify the result set and see that the returned Product IDs are in the SalesOrderDetail take one of the Product IDs returned (I used 707) and run against the SalesOrderDetail.
select * from Sales.SalesOrderDetail
where ProductID = 707
–3083 rows returned
The following Venn Diagram shows what records are returned using the examples above. Except returns the results that are in the orange section of the circle. Intersect returns the results that are in the orange and blue shaded section.
For complete information on EXCEPT and INTERSECT see MSDN – https://msdn.microsoft.com/en-us/library/ms188055.aspx
The post T-SQL EXCEPT and INTERSECT appeared first on BI and Predictive Analytics.
