Summary: in this tutorial, you will learn how to use the SQL Server
INTERSECT operator to combine result sets of two input queries and return the distinct rows that appear in both inputs.
Introduction to SQL Server
The SQL Server
INTERSECT combines result sets of two or more queries and returns distinct rows that are output by both queries.
The following illustrates the syntax of the SQL Server
Similar to the
UNION operator, the queries in the syntax above must conform to the following rules:
- Both queries must have the same number and order of columns.
- The data type of the corresponding columns must be the same or compatible.
The following picture illustrates the
In this illustration, we had two result sets T1 and T2:
- T1 result set includes 1, 2, and 3.
- T2 result set includes 2, 3, and 4.
The intersection of T1 and T2 result sets returns the distinct rows which are 2 and 3.
Consider the following query:
The first query finds all cities where customers locate and the second query finds the cities of the stores. The whole query, which uses
INTERSECT, finds the common cities of customers and stores, which are the cities output by both input queries.
Notice that we added the
ORDER BY clause to the last query to sort the result set.
In this tutorial, you have learned how to use the SQL Server
INTERSECT opeartor to return the intersection of the result sets of two queries.