37. Except/Minus

EXCEPT (or MINUS in Oracle used by ROI CRM) is a set operation that will output a list of rows that are present in the first query's results but not the second query's results. You can think of this as performing the opposite of INTERSECT. As an example, let's say we wanted a list of all donors who have given a gift in calendar year 2022 except for those that live in Iowa and Kansas. We could accomplish this using EXCEPT as follows:

select
    account_id
from transactions
where year(transaction_date) = 2022
EXCEPT
select
    account_id
from addresses
where state in ('IA','KS')

This query tells the database, "Build a list of all account IDs with a gift in 2022. Then remove from that list every account ID with an address in Iowa or Kansas." Like INTERSECT and UNION, EXCEPT will remove duplicate rows from the output. And like all set operations, EXCEPT 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, EXCEPT is most commonly used when performing some time of omit or suppression. For example, if we wanted a list of donors who meet some complex set of giving criteria but who do not have the "Do Not Contact" marker on their account, we could use EXCEPT to do this.

Exercise 1: Except

Write an SQL query using EXCEPT to build a list of all Direct Mail source codes that do not have any gifts of $500 or more credited to them.