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.

Practice

1. Why do we use set operations like UNION and INTERSECT in SQL?

easy

A. To create new tables permanently

B. To delete rows based on conditions

C. To update values in a single table

D. To combine or compare rows from two or more tables easily

Solution

1. Step 1: Understand the purpose of set operations

   Set operations like UNION and INTERSECT are designed to combine or compare rows from multiple tables.

2. Step 2: Identify what set operations do not do

   They do not create tables, update, or delete rows; those are different SQL commands.

3. Final Answer:

   To combine or compare rows from two or more tables easily -> Option D

4. Quick Check:

   Set operations combine or compare data [OK]

Hint: Set operations combine or compare rows from tables [OK]

Common Mistakes:

• Confusing set operations with data modification commands
• Thinking UNION creates a new permanent table
• Assuming INTERSECT deletes rows

2. Which of the following is the correct syntax to combine two SELECT queries using UNION in SQL?

easy

A. UNION SELECT * FROM table1, SELECT * FROM table2;

B. SELECT * FROM table1 JOIN UNION SELECT * FROM table2;

C. SELECT * FROM table1 UNION SELECT * FROM table2;

D. SELECT * FROM table1 AND SELECT * FROM table2 UNION;

Solution

1. Step 1: Recall correct UNION syntax

   The correct syntax is to write one SELECT query, then UNION, then another SELECT query.

2. Step 2: Check each option for syntax errors

   SELECT * FROM table1 UNION SELECT * FROM table2; follows the correct syntax. Options A, B, and D have incorrect keywords or order.

3. Final Answer:

   SELECT * FROM table1 UNION SELECT * FROM table2; -> Option C

4. Quick Check:

   Correct UNION syntax = SELECT * FROM table1 UNION SELECT * FROM table2; [OK]

Hint: UNION joins two SELECT queries directly [OK]

Common Mistakes:

• Adding JOIN keyword with UNION
• Using commas instead of UNION
• Placing UNION at the end incorrectly

3. Given two tables:
Table A: {1, 2, 3}
Table B: {2, 3, 4}
What is the result of SELECT * FROM A INTERSECT SELECT * FROM B;?

medium

A. {2, 3}

B. {1, 2, 3}

C. {1, 4}

D. {1, 2, 3, 4}

Solution

1. Step 1: Understand INTERSECT operation

   INTERSECT returns only rows present in both tables.

2. Step 2: Find common elements in Table A and Table B

   Common elements are 2 and 3.

3. Final Answer:

   {2, 3} -> Option A

4. Quick Check:

   INTERSECT = common rows [OK]

Hint: INTERSECT returns only common rows [OK]

Common Mistakes:

• Confusing INTERSECT with UNION
• Including all rows from both tables
• Mixing up EXCEPT with INTERSECT

4. You wrote this SQL query:
SELECT * FROM table1 UNION table2;
What is the error and how to fix it?

medium

A. Missing SELECT before table2; fix by adding SELECT * FROM table2

B. UNION cannot be used with tables; use JOIN instead

C. UNION requires parentheses around queries

D. No error; query runs fine

Solution

1. Step 1: Identify syntax error in UNION usage

   UNION requires two complete SELECT statements, but second query lacks SELECT.

2. Step 2: Correct the query syntax

   Add SELECT * FROM before table2 to fix the error.

3. Final Answer:

   Missing SELECT before table2; fix by adding SELECT * FROM table2 -> Option A

4. Quick Check:

   UNION needs two SELECTs [OK]

Hint: UNION needs two full SELECT queries [OK]

Common Mistakes:

• Omitting SELECT in second query
• Using UNION with tables directly
• Adding unnecessary parentheses

5. You have two customer lists:
List A: customers who bought product X
List B: customers who bought product Y
How do you find customers who bought either product X or Y but not both using set operations?

hard

A. SELECT * FROM A UNION SELECT * FROM B

B. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A)

C. SELECT * FROM A INTERSECT SELECT * FROM B

D. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM A INTERSECT SELECT * FROM B)

Solution

1. Step 1: Understand the problem

   We want customers who bought product X or Y but not both (exclusive customers).

2. Step 2: Use EXCEPT and UNION to find exclusive customers

   Find customers in A but not in B, and customers in B but not in A, then combine them with UNION.

3. Step 3: Check other options

   SELECT * FROM A UNION SELECT * FROM B gives all customers who bought either product (including both). SELECT * FROM A INTERSECT SELECT * FROM B gives only those who bought both. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM A INTERSECT SELECT * FROM B) gives all customers who bought product X.

4. Final Answer:

   (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A) -> Option B

5. Quick Check:

   Exclusive customers = (A EXCEPT B) UNION (B EXCEPT A) [OK]

Hint: Use EXCEPT both ways, then UNION results [OK]

Common Mistakes:

• Using UNION alone includes both customers
• Using INTERSECT returns only common customers
• Confusing EXCEPT with INTERSECT