INTERSECT Clause
The INTERSECT clause returns only those rows that result from both the first and the second queries. The queries must match the number of columns, order, and type. The result of INTERSECT can contain duplicate rows.
Multiple INTERSECT statements are executed left to right if parentheses are not specified. The INTERSECT operator has a higher priority than the UNION and EXCEPT clauses.
The condition could be any expression based on your requirements.
Examples
Here is a simple example that intersects the numbers 1 to 10 with the numbers 3 to 8:
Result:
INTERSECT is useful if you have two tables that share a common column (or columns). You can intersect the results of two queries, as long as the results contain the same columns. For example, suppose we have a few million rows of historical cryptocurrency data that contains trade prices and volume:
Now suppose we have a table named holdings that contains a list of cryptocurrencies that we own, along with the number of coins:
We can use INTERSECT to answer questions like "Which coins do we own that have traded at a price greater than $100?":
Result:
This means at some point in time, Bitcoin and Ethereum traded above 100 (at least using the data we have here in this example).
INTERSECT DISTINCT
Notice in the previous query we had multiple Bitcoin and Ethereum holdings that traded above $100. It might be nice to remove duplicate rows (since they only repeat what we already know). You can add DISTINCT to INTERSECT to eliminate duplicate rows from the result:
Result:
See Also