36. Intersect

The INTERSECT set operation compares the output of two queries and returns a list of all rows that are present in both queries. We have already seen an example of INTERSECT in action in the intro chapter to this section, reproduced below:

select
    account_id
from addresses
where state = 'GA'
INTERSECT
select
    account_id
from transactions
where amount >= 500

This query tells the database, "Build a list of account IDs in the state of Georgia, then build a second list of all account IDs that have given gifts of $500 or more. Finally, output for me the list of account IDs that appear on both lists." Note that INTERSECT will remove duplicate rows from the output. Like UNION, you can use INTERSECT ALL to retain duplicates (in most database systems), though this is very rarely of use.

Like all other set operations, INTERSECT can operate on queries with multiple columns and requires that:

• There are the same number of columns in both queries, and
• The columns are of compatible data types by position

In fundraising, INTERSECT is most useful when you have multiple lists of donors and you want to determine who is on all of them. This often takes the form of questions like, "Which donors received both the August Appeal and the September Renewal?" As we saw with UNION, INTERSECT is by no means the only (or even the best) way to answer a question like this. But it is one of the ways you can answer such a question, and is an important tool to be familiar with.

Exercise 1: Intersect

Write an SQL query using INTERSECT to generate a list of donors whose smallest gift on file is at least $100, who also have an email address on file. Output should include account_id only.