Introduction to SET Operators

Introduction

In SQL, SET operators are powerful tools that allow you to combine the results of two or more SELECT queries into a single result set.

While JOINs combine columns from different tables horizontally (adding more columns), SET operators combine rows from different queries vertically (adding more rows).

In this tutorial, you will learn:

What SET operators are and why they exist
The difference between SET operators and JOINs
An overview of the four main SET operators
When to use SET operators in real-world scenarios

What are SET Operators?

SET operators are SQL commands that combine the results of two or more SELECT statements into a single result set. They work on the principle of set theory from mathematics.

The Four Main SET Operators

Operator	Description
UNION	Combines results and removes duplicates
UNION ALL	Combines results and keeps all rows (including duplicates)
EXCEPT	Returns rows from the first query that don't exist in the second
INTERSECT	Returns only rows that exist in both queries

SET Operators at a Glance

Overview diagram showing all four SET operators: UNION combines and deduplicates, UNION ALL combines and keeps all, EXCEPT returns rows in first but not second, INTERSECT returns rows in both

SET Operators vs JOINs

A common point of confusion is understanding the difference between SET operators and JOINs. They serve completely different purposes.

JOINs: Horizontal Combination

JOINs combine columns from different tables based on a relationship:

Generated visual representation from text diagram for SET Operators vs JOINs.

SET Operators: Vertical Combination

SET operators stack rows from different queries:

Key Differences

Aspect	JOINs	SET Operators
Combines	Columns (horizontally)	Rows (vertically)
Requires	Related keys between tables	Same column structure
Result width	Sum of columns	Same as original queries
Purpose	Relate data from different tables	Combine similar data from different sources

When to Use SET Operators

SET operators are particularly useful in several scenarios:

1. Combining Data from Multiple Sources

When you have similar data in different tables or databases:

-- Combine customers from different regions
SELECT customer_name, email FROM us_customers
UNION
SELECT customer_name, email FROM eu_customers;

2. Creating Reference Lists

When you need a comprehensive list of unique values:

-- All cities where we have customers or sellers
SELECT customer_city AS city FROM customers
UNION
SELECT seller_city AS city FROM sellers;

3. Finding Differences

When you need to identify what exists in one set but not another:

-- Find customers who haven't placed orders
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

4. Finding Common Elements

When you need to find overlap between two sets:

-- Find products that are both high-rated AND high-selling
SELECT product_id FROM top_rated_products
INTERSECT
SELECT product_id FROM best_selling_products;

5. Data Reconciliation

When comparing two data sources to find discrepancies:

-- Find records in source that don't exist in target
SELECT id, name FROM source_table
EXCEPT
SELECT id, name FROM target_table;

Real-World Examples

Let's look at practical examples using our e-commerce dataset.

Example 1: All Locations (Customers + Sellers)

Find all unique cities where either customers or sellers are located:

-- All unique cities from customers and sellers
SELECT customer_city AS city, customer_state AS state 
FROM olist_customers_dataset
UNION
SELECT seller_city AS city, seller_state AS state 
FROM olist_sellers_dataset
LIMIT 20;

Example 2: Preview of SET Operations

Here's a quick preview of what each operator does with sample data:

Query 1: Cities in São Paulo state

customer_city: sao paulo, campinas, santos

Query 2: Cities with sellers

seller_city: sao paulo, curitiba, santos

Operator	Result
UNION	sao paulo, campinas, santos, curitiba
UNION ALL	sao paulo, campinas, santos, sao paulo, curitiba, santos
EXCEPT	campinas (only in Query 1)
INTERSECT	sao paulo, santos (in both)

SET Operators in Context

Relationship to Set Theory

SQL SET operators are directly based on mathematical set theory:

SQL Operator	Set Theory Equivalent	Symbol
UNION	Union	A ∪ B
INTERSECT	Intersection	A ∩ B
EXCEPT	Difference	A - B

Order of Operations

When combining multiple SET operators, they are evaluated in this order:

INTERSECT (highest priority)
UNION / UNION ALL / EXCEPT (left to right)

Use parentheses to control the order:

-- Without parentheses
Query1 UNION Query2 INTERSECT Query3
-- Evaluates as: Query1 UNION (Query2 INTERSECT Query3)

-- With parentheses
(Query1 UNION Query2) INTERSECT Query3
-- Now UNION is evaluated first

Summary

Key Takeaways

SET operators combine results from multiple SELECT statements vertically (adding rows)
Four main operators:

UNION - combines and removes duplicates
UNION ALL - combines and keeps duplicates
EXCEPT - returns rows only in the first query
INTERSECT - returns rows in both queries

SET operators ≠ JOINs:

JOINs combine columns horizontally
SET operators stack rows vertically

Requirements:

Same number of columns in all queries
Compatible data types in corresponding columns

Common use cases:

Combining data from multiple sources
Finding differences between datasets
Creating comprehensive reference lists
Data reconciliation and delta detection

What's Next?

In the next lesson, we'll learn about the rules and syntax that govern SET operators, including column requirements and data type compatibility.