Set Operators for Data Intersection

In this lesson, you will learn how to combine results from multiple independent queries into a single output using SQL set operators.

Why Use Set Operators?

Up until now, you have used JOIN to connect tables based on related columns. However, sometimes you don't need to join tables horizontally. Instead, you might want to stack result sets vertically or find the overlap between two completely different lists. Set operators treat your query results like mathematical sets, allowing you to manipulate how they interact.

The Three Core Operators

There are three primary operators used to combine result sets:

1. UNION: This stacks two result sets on top of each other and removes any duplicate rows. If you want to keep the duplicates, use UNION ALL.
2. INTERSECT: This returns only the rows that appear in both query results. Think of this as the "common ground" between two lists.
3. EXCEPT: This returns rows that exist in the first query but not in the second.

A Concrete Illustration

Imagine you have a list of Customers who bought "Product A" and another list of Customers who bought "Product B".

To find the customers who bought both products, you would use INTERSECT:

SELECT customer_id FROM Sales WHERE product_name = 'Product A'
INTERSECT
SELECT customer_id FROM Sales WHERE product_name = 'Product B';

To find customers who bought "Product A" but have never purchased "Product B", you would use EXCEPT:

SELECT customer_id FROM Sales WHERE product_name = 'Product A'
EXCEPT
SELECT customer_id FROM Sales WHERE product_name = 'Product B';

Important Rules

For these operators to work, your queries must follow two strict rules:

• Column Count: Every SELECT statement must have the same number of columns.
• Data Types: The data types of the corresponding columns must be compatible (e.g., you cannot UNION a column of dates with a column of integers).

Unlike JOIN, which matches columns side-by-side, set operators combine rows based on their vertical relationship. They are powerful tools for comparing lists and filtering data based on membership rather than specific attributes.

Key Takeaway: Set operators (UNION, INTERSECT, EXCEPT) allow you to compare and combine result sets vertically, provided your queries return the same number and type of columns.