Why set operations are needed in SQL - Why It Works This Way

Overview - Why set operations are needed

What is it?

Set operations in SQL are commands that combine results from two or more queries into a single result. They allow you to work with multiple sets of data as if they were one. Common set operations include UNION, INTERSECT, and EXCEPT, which help merge, find common parts, or find differences between data sets. These operations treat query results like groups of items, similar to how math sets work.

Why it matters

Without set operations, combining or comparing data from different tables or queries would be much harder and require complex, repetitive code. Set operations simplify these tasks, making data analysis faster and more accurate. They help answer questions like 'What customers bought either product A or B?' or 'Which employees are in both departments?' This makes data handling more powerful and efficient in real-world applications.

Where it fits

Before learning set operations, you should understand basic SQL queries, SELECT statements, and how to filter data with WHERE clauses. After mastering set operations, you can explore advanced SQL topics like joins, subqueries, and window functions to manipulate and analyze data in more complex ways.

Mental Model

Core Idea

Set operations let you combine or compare groups of data results as if they were simple collections, making complex data questions easy to answer.

Think of it like...

Imagine you have two baskets of fruits. Set operations are like combining the baskets, finding fruits common to both, or picking fruits that are only in one basket but not the other.

┌─────────────┐   ┌─────────────┐
│   Query 1   │   │   Query 2   │
└──────┬──────┘   └──────┬──────┘
       │                 │
       │                 │
       ▼                 ▼
   ┌─────────────────────────┐
   │     Set Operation        │
   │  (UNION, INTERSECT, etc) │
   └────────────┬────────────┘
                │
                ▼
        ┌─────────────┐
        │ Combined    │
        │ Result Set  │
        └─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic Query Results

Concept: Learn what a query result set is and how SQL returns data.

When you write a SQL SELECT statement, it retrieves rows of data from a table or tables. This output is called a result set. Each result set is like a list of items that match your query conditions.

Result

You get a table of rows and columns showing the data you asked for.

Understanding that each query produces a set of rows is key to seeing how these sets can be combined or compared.

2

Foundation

Introduction to Multiple Queries

Concept: Recognize that you can run multiple queries separately to get different data sets.

You can write several SELECT statements to get different lists of data. For example, one query might list customers from city A, another from city B. Each query runs independently and returns its own result set.

Result

Two or more separate lists of data, each from its own query.

Seeing queries as separate sets prepares you to combine or compare them using set operations.

3

Intermediate

Combining Results with UNION

🤔Before reading on: do you think UNION keeps duplicate rows or removes them? Commit to your answer.

Concept: UNION merges two result sets into one, removing duplicates by default.

UNION takes the rows from two queries and puts them together into a single list. If the same row appears in both queries, UNION shows it only once. This helps when you want a combined list without repeats.

Result

A single result set with all unique rows from both queries.

Knowing UNION removes duplicates helps avoid unexpected repeated data in combined results.

4

Intermediate

Finding Common Data with INTERSECT

🤔Before reading on: do you think INTERSECT returns rows in either query or only those in both? Commit to your answer.

Concept: INTERSECT returns only the rows that appear in both result sets.

INTERSECT compares two query results and returns only the rows that exist in both. This is useful to find common data, like customers who bought both product A and product B.

Result

A result set showing only the shared rows between the two queries.

Understanding INTERSECT helps you find overlaps in data sets quickly and clearly.

5

Intermediate

Excluding Data with EXCEPT

🤔Before reading on: do you think EXCEPT returns rows from the first query that are also in the second, or only those unique to the first? Commit to your answer.

Concept: EXCEPT returns rows from the first query that do not appear in the second.

EXCEPT compares two result sets and returns only the rows found in the first query but missing from the second. This helps identify differences, like customers who bought product A but not product B.

Result

A result set with rows unique to the first query.

Knowing how EXCEPT works lets you spot differences between data sets without complex filtering.

6

Advanced

Rules for Using Set Operations

🤔Before reading on: do you think queries combined with set operations can have different numbers of columns? Commit to your answer.

Concept: Set operations require queries to have the same number of columns and compatible data types.

When using UNION, INTERSECT, or EXCEPT, each query must select the same number of columns, and the columns must be of similar types (like numbers or text). This ensures the combined result makes sense and can be processed correctly.

Result

Queries that follow these rules run successfully; those that don't cause errors.

Understanding these rules prevents common errors and ensures your set operations work as expected.

7

Expert

Performance and Optimization of Set Operations

🤔Before reading on: do you think set operations always perform faster than joins? Commit to your answer.

Concept: Set operations can impact performance differently than joins; understanding execution helps optimize queries.

Set operations like UNION and INTERSECT may cause the database to sort or scan data to remove duplicates or find matches. This can be slower than joins in some cases. Knowing when to use set operations versus joins, and how indexes affect them, helps write efficient queries.

Result

Better query performance and resource use when set operations are applied wisely.

Knowing the internal cost of set operations guides you to write faster, more scalable SQL.

Under the Hood

Set operations work by taking the output rows from each query and treating them as mathematical sets. The database engine compares these sets according to the operation: UNION merges and removes duplicates, INTERSECT finds common rows, and EXCEPT finds differences. Internally, this often involves sorting or hashing rows to efficiently compare them.

Why designed this way?

Set operations were designed to simplify combining and comparing data without complex joins or subqueries. They follow mathematical set theory to provide clear, predictable results. This design makes SQL more expressive and easier to use for common data tasks.

┌───────────────┐      ┌───────────────┐
│   Query 1     │      │   Query 2     │
│  Result Set   │      │  Result Set   │
└───────┬───────┘      └───────┬───────┘
        │                      │
        │                      │
        ▼                      ▼
   ┌─────────────────────────────┐
   │  Database Engine Processes   │
   │  - Sorts or hashes rows      │
   │  - Compares rows per set op  │
   └─────────────┬───────────────┘
                 │
                 ▼
          ┌─────────────┐
          │ Final Result│
          └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does UNION always keep duplicate rows? Commit to yes or no.

Common Belief: UNION just stacks two query results without removing duplicates.

Reality: UNION removes duplicate rows by default; to keep duplicates, UNION ALL must be used.

Why it matters: Assuming UNION keeps duplicates can lead to unexpected missing rows or incorrect counts in combined data.

Quick: Does INTERSECT return rows from either query or only those in both? Commit to your answer.

Common Belief: INTERSECT returns all rows from both queries combined.

Reality: INTERSECT returns only rows that appear in both queries.

Why it matters: Misunderstanding INTERSECT can cause wrong assumptions about data overlap and lead to incorrect analysis.

Quick: Can set operations combine queries with different numbers of columns? Commit to yes or no.

Common Belief: You can combine any queries with set operations regardless of column count.

Reality: All queries combined with set operations must have the same number of columns with compatible data types.

Why it matters: Ignoring this causes syntax errors and confusion when queries fail to run.

Quick: Are set operations always faster than joins? Commit to yes or no.

Common Belief: Set operations are always more efficient than joins.

Reality: Set operations can be slower than joins depending on data size and indexing because of sorting or hashing overhead.

Why it matters: Assuming set operations are always faster can lead to poor performance in large or complex queries.

Expert Zone

1

Set operations implicitly remove duplicates unless UNION ALL is used, which can affect query results subtly.

2

The order of rows in set operation results is not guaranteed unless an ORDER BY is applied after the operation.

3

Set operations require careful matching of column data types and order, which can cause subtle bugs if overlooked.

When NOT to use

Set operations are not suitable when you need to combine data with different columns or when you want to join related data based on keys. In such cases, JOINs or subqueries are better alternatives.

Production Patterns

In real systems, UNION ALL is often preferred over UNION for performance when duplicates are not a concern. INTERSECT and EXCEPT are used for data validation, auditing, and finding differences between datasets, such as comparing current and previous records.

Connections

Set Theory (Mathematics)

Set operations in SQL directly implement mathematical set theory concepts like union, intersection, and difference.

Understanding mathematical sets clarifies how SQL set operations combine and compare data, making the logic intuitive.

Relational Algebra

Set operations are fundamental operators in relational algebra, the theoretical foundation of relational databases.

Knowing relational algebra helps understand why set operations behave consistently and how they fit into query optimization.

Data Merging in Spreadsheet Software

Set operations are similar to merging or comparing lists in spreadsheets, like combining rows or finding common entries.

Recognizing this similarity helps non-technical users grasp SQL set operations by relating them to familiar spreadsheet tasks.

Common Pitfalls

#1 Using UNION when duplicates should be kept.

Wrong approach:

SELECT name FROM customers_2023
UNION
SELECT name FROM customers_2024;

Correct approach:

SELECT name FROM customers_2023
UNION ALL
SELECT name FROM customers_2024;

Root cause:

Not knowing UNION removes duplicates by default, leading to loss of repeated data.

#2 Combining queries with different column counts.

Wrong approach:

SELECT id, name FROM employees
UNION
SELECT id FROM managers;

Correct approach:

SELECT id, name FROM employees
UNION
SELECT id, name FROM managers;

Root cause:

Misunderstanding that set operations require matching column numbers and types.

#3 Expecting set operation results to be ordered.

Wrong approach:

SELECT city FROM customers_2023
UNION
SELECT city FROM customers_2024;

Correct approach:

SELECT city FROM customers_2023
UNION
SELECT city FROM customers_2024
ORDER BY city;

Root cause:

Assuming SQL preserves order without explicit ORDER BY after set operations.

Key Takeaways

Set operations let you combine or compare query results as groups of data, simplifying complex data questions.

UNION merges results and removes duplicates unless UNION ALL is used to keep them.

INTERSECT finds common rows, and EXCEPT finds rows unique to one query, enabling powerful data comparisons.

All queries combined with set operations must have the same number of columns and compatible data types.

Understanding set operations' behavior and performance helps write efficient, correct SQL queries.