34. What Are Set Operations?

Set operations combine the results of two select queries, treating them as sets of rows. We're going to cover three types of set operations in this course:

• UNION/UNION ALL – Combines the results from both queries
• INTERSECT – Returns all rows that appear in both queries
• EXCEPT/MINUS – Returns all rows from the first query that are not in the second query

Let's look at a quick example using INTERSECT. If we wanted to get a list of all donors who have given a gift of $500 or more who also live in Georgia, we could accomplish that as follows:

This example demonstrates the use of set operations. The keyword (intersect in this case) is placed between two select statements and performs an operation on the results of the two queries. In this example, the query is telling the database, "Build a list of all account IDs in the state of Georgia, then build a second list of all account IDs for donors who have given $500 or more, then tell me which IDs are on both lists."

There are several ways we could have accomplished this task (an inner join, using in with a subquery, etc.) and I am by no means suggesting that using intersect is the best way to do it. This is just an example to demonstrate the principles of set operations. Hopefully by now you're starting to understand the flexibility of SQL. There are usually multiple ways to complete a task, and it is up to you determine which method fits your use case and skillset best.

Notes About Set Operations

When using set operations, keep the following in mind:

• The queries involved must have the same number of columns with compatible data types in each position.
• The result is a single result set that combines or filters the underlying queries.
• Set operations work row by row, not on individual values.

We will go over the ins and outs of the set operations in the following chapters.