Learning Snowflake SQL and Scripting

Chapter 4. Working with Sets

Although you can interact with your data one row at a time, relational databases are really all about sets. This chapter explores the use of set operators, which allow you to combine data from multiple result sets. I’ll start with a quick overview of set theory and then move on to show how the set operators union, intersect, and except can be used to blend multiple data sets together.

Set Theory Primer

You may recall from an early math class seeing diagrams such as the one shown in Figure 4-1.

Figure 4-1. Union of two sets

The shaded area in Figure 4-1 represents the union of sets A and B, with the overlapping area included just once. Here’s another way of illustrating the union operation, using two sets of  integers:

A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
A union B = {1, 2, 3, 4, 5, 7, 9}

In this example, set A contains 5 integers, and set B contains 4 integers. Each set has a couple of unique values, but both sets share the values 7 and 9. The union of A and B yields a total of 7 values, with the integers 7 and 9 included just once.

Next, let’s look at the area shared by two sets, known as the intersection. Figure 4-2 shows the graphical depiction of an intersection.

Figure 4-2. Intersection of two sets

Using the same sets of integers as the last ...