Introduction to Set Operations

Apply basic set theory principles like union, intersection, and difference to relational data.

In this lesson, you will learn how to combine and compare tables using fundamental set operations inspired by mathematics.

The Set Perspective

In relational databases, we treat a table as a set of rows. Since tables are sets, we can perform operations on them to derive new information, just as you would with groups of numbers or objects in math class. There are three primary ways we combine these "sets" of data:

Union (UNION): This combines two sets into one. Imagine you have a list of customers in "New York" and a list of customers in "California." If you perform a UNION, you get a single list containing everyone from both locations.
Intersection (INTERSECT): This finds only the items that appear in both sets. If you want to find employees who work in both the "Sales" department AND the "Marketing" department, an intersection gives you the specific individuals present in both lists.
Difference (EXCEPT): This takes one set and removes the items that are also found in another. If you have a list of all current students, and you subtract a list of students who have already graduated, you are left with only the currently enrolled students.

A Quick Illustration

Imagine two small tables, Table_A and Table_B. To perform these operations, the tables must be "compatible"—they must have the same number of attributes (columns) with the same data types.

-- This brings everything from Table_A and Table_B together
SELECT name FROM Table_A
UNION
SELECT name FROM Table_B;

When you perform these operations, the database looks at the entire row as a single unit. It asks: "Is this specific combination of values present in the other table?" This logic allows us to perform powerful filtering and consolidation without writing complex, nested search queries.

By mastering these three operations, you move beyond simply retrieving data and start performing logical analysis on your relational sets.

Key Takeaway: Set operations allow you to treat tables as collections, enabling you to merge (UNION), find commonalities (INTERSECT), or identify unique differences (EXCEPT) between your data groups.