SQL INTERSECT, Set operations

What is the SQL INTERSECT set operation

The SQL INTERSECT set operation returns the distinct rows from two or more queries that appear in each query’s results. Unlike the SQL UNION set operation that returns all rows from both queries, the SQL INTERSECT operation only returns the rows that appear in both queries. Visualized with a Venn diagram, the SQL INTERSECT operation looks like:

SQL INTERSECT, Set operations

For example, suppose we apply the INTERSECT set operation to two queries: the ProductID and Name column from Production.Product for all rows where the ProductID is less than 300 and another query for the same columns but for all rows where the ProductID is greater than or equal to 100:

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID < 400

INTERSECT

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID >= 100

The results only contain those rows that appeared in the results for each query. In this case, any row of Production.Product with a ProductID between 100 and 399 would appear in the SQL INTERSECT operation result set. If you look at the results, those are the exact rows that results from the complete query.

Let’s look at another example. Take two queries: one query for the BusinessEntityID, NationalIDNumber, and JobTitle columns from HumanResources.Employee where the BusinessEntityID is less than 50 and another query for the same columns but for rows where the JobTitle is either “Chief Executive Officer” or “Research and Development Manager”:

SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID < 50

INTERSECT

SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE JobTitle IN ('Chief Executive Officer', 'Research and Development Manager')

The results of this SQL INTERSECT operation only include the rows that appear appear in both queries. So, if there was a row with a BusinessEntityID greater than 50 and the JobTitle “Research and Development Manager” then that row would not appear in the results of the INTERSECT operation.

Practice Problems

Using the examples from this section as a resource, try to complete the following practice problems.

  1. Use the INTERSECT operation on the following two queries: the SalesOrderID and TotalDue columns from Sales.SalesOrderHeader for all rows where the SalesOrderID is less than 44,000 and another query for the same columns but for rows where the TotalDue is less than $6,000.
  2. Use the INTERSECT operation for two queries. The first query will be all columns from Production.Product for all rows with a ProductID less than 50. The second query will be all columns from Production.Product for all rows with a ProductID greater than 200. What do the results look like? Why?

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *