Why combining result sets is useful in MySQL - Why It Works This Way

Overview - Why combining result sets is useful

What is it?

Combining result sets means joining together data from two or more queries into one list. This helps you see information from different places all at once. For example, you might want to see all customers and all suppliers in one list. Combining results makes it easier to compare, analyze, or report data without running separate queries.

Why it matters

Without combining result sets, you would have to look at each query's output separately and manually merge the information. This wastes time and can cause mistakes. Combining results saves effort, helps spot patterns, and supports better decisions by showing a complete picture quickly.

Where it fits

Before learning this, you should understand basic SQL SELECT queries and how to filter data. After this, you can learn about more advanced data operations like JOINs, subqueries, and data aggregation to analyze relationships between tables.

Mental Model

Core Idea

Combining result sets lets you merge multiple lists of data into one, making it easier to work with and understand all the information together.

Think of it like...

Imagine you have two guest lists for a party: one for friends and one for coworkers. Combining result sets is like putting both lists on one big sheet so you can see everyone invited at once.

┌───────────────┐   ┌───────────────┐
│ Friends List  │   │ Coworkers List│
│ Alice        │   │ Bob           │
│ Charlie      │   │ Dana          │
└──────┬────────┘   └──────┬────────┘
       │                   │
       └───────┬───────────┘
               │ Combined List
               │ Alice
               │ Charlie
               │ Bob
               │ Dana
               └─────────────

Build-Up - 7 Steps

1

Foundation

Understanding Basic SELECT Queries

Concept: Learn how to retrieve data from a single table using SELECT.

A SELECT query asks the database to give you rows from a table. For example, SELECT name FROM customers; will list all customer names.

Result

A list of names from the customers table.

Knowing how to get data from one table is the first step before combining data from multiple queries.

2

Foundation

What is a Result Set?

Concept: A result set is the list of rows returned by a query.

When you run a query, the database sends back a table of data. This table is called a result set. It has rows and columns like a spreadsheet.

Result

You see a table of data matching your query conditions.

Understanding that queries produce result sets helps you see why combining them can be useful.

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 two queries and stacks their results on top of each other. For example: SELECT name FROM customers UNION SELECT name FROM suppliers; This shows all unique names from both tables.

Result

A combined list of unique names from customers and suppliers.

Knowing that UNION removes duplicates helps you avoid repeated data when combining lists.

4

Intermediate

Using UNION ALL to Keep Duplicates

🤔Before reading on: do you think UNION ALL is faster or slower than UNION? Commit to your answer.

Concept: UNION ALL combines result sets but keeps all duplicates, and is faster than UNION.

If you want to see every row from both queries, including duplicates, use UNION ALL: SELECT name FROM customers UNION ALL SELECT name FROM suppliers; This shows all names, even if they appear in both tables.

Result

A combined list including duplicates from both tables.

Understanding UNION ALL's behavior helps when duplicates are meaningful or performance matters.

5

Intermediate

Matching Columns for Combining Results

Concept: To combine result sets, queries must select the same number of columns with compatible types.

When using UNION or UNION ALL, each SELECT must have the same number of columns, and the columns should be similar types. For example, SELECT name, city FROM customers UNION SELECT name, city FROM suppliers works, but SELECT name FROM customers UNION SELECT city FROM suppliers does not.

Result

Queries run successfully only if columns match in number and type.

Knowing this prevents errors and ensures combined data makes sense.

6

Advanced

Combining Results for Reporting and Analysis

🤔Before reading on: do you think combining result sets can replace JOINs? Commit to your answer.

Concept: Combining result sets is useful for stacking similar data, but JOINs are for merging related data side-by-side.

Use UNION when you want to list data from multiple sources as one list, like all sales from different regions. Use JOIN when you want to combine related data, like customer info with their orders. Combining results helps create unified reports from separate queries.

Result

Clear reports showing combined data from multiple sources.

Understanding when to combine results versus when to join tables improves data analysis choices.

7

Expert

Performance Considerations When Combining Results

🤔Before reading on: do you think UNION or UNION ALL is more efficient? Commit to your answer.

Concept: UNION removes duplicates which requires extra work, making UNION ALL faster for large datasets.

When combining large result sets, UNION must check for duplicates, which slows queries. UNION ALL skips this step and runs faster. Choosing the right one affects database speed and resource use.

Result

Better query performance by selecting the appropriate combining method.

Knowing the performance tradeoffs helps optimize queries in real systems.

Under the Hood

When you run a UNION query, the database executes each SELECT separately, then merges the results. For UNION, it scans the combined data to remove duplicates, often using sorting or hashing. UNION ALL skips duplicate removal and simply appends results. The database ensures column types match to align data correctly.

Why designed this way?

UNION was designed to provide a simple way to combine similar data sets while avoiding repeated rows by default, which is often what users want. UNION ALL was added later to give control when duplicates matter or performance is critical. This design balances ease of use with flexibility and efficiency.

┌───────────────┐   ┌───────────────┐
│ SELECT Query 1│   │ SELECT Query 2│
│ Result Set A  │   │ Result Set B  │
└──────┬────────┘   └──────┬────────┘
       │                   │
       └───────┬───────────┘
               │ Combine Results
               │ (UNION removes duplicates)
               │ (UNION ALL keeps duplicates)
               ▼
        ┌───────────────┐
        │ Final Result  │
        │ Combined Data │
        └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does UNION always keep duplicates? Commit yes or no.

Common Belief: UNION keeps all rows from both queries, including duplicates.

Reality: UNION removes duplicate rows by default, showing only unique rows.

Why it matters: Assuming UNION keeps duplicates can lead to unexpected missing data and wrong analysis.

Quick: Can you combine queries with different numbers of columns using UNION? Commit yes or no.

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

Reality: UNION requires queries to have the same number of columns with compatible types.

Why it matters: Trying to combine mismatched queries causes errors and confusion.

Quick: Is UNION ALL slower than UNION? Commit yes or no.

Common Belief: UNION ALL is slower because it combines all rows.

Reality: UNION ALL is faster because it does not remove duplicates.

Why it matters: Misunderstanding this can cause inefficient queries and slow applications.

Quick: Can combining result sets replace JOINs? Commit yes or no.

Common Belief: Combining result sets with UNION is the same as joining tables.

Reality: UNION stacks rows vertically; JOIN combines columns horizontally based on relationships.

Why it matters: Confusing these leads to wrong query design and incorrect data.

Expert Zone

1

UNION's duplicate removal can cause subtle bugs if data has hidden differences like trailing spaces or case sensitivity.

2

The order of queries in UNION affects which rows appear first, but SQL does not guarantee order unless ORDER BY is used on the final result.

3

Using UNION with large datasets can cause high memory usage due to sorting or hashing for duplicate removal.

When NOT to use

Avoid combining result sets with UNION when you need to relate data side-by-side; use JOINs instead. Also, if you need to preserve duplicates for counting or analysis, prefer UNION ALL. For complex data merging, consider subqueries or temporary tables.

Production Patterns

In real systems, UNION is used to merge logs from different sources, combine search results from multiple tables, or unify data from partitioned tables. UNION ALL is preferred in ETL processes where duplicates are expected and performance is critical.

Connections

Set Theory

UNION in SQL directly corresponds to the union operation in set theory, combining elements from two sets.

Understanding set theory helps grasp why UNION removes duplicates and how combining sets works logically.

Data Integration

Combining result sets is a basic form of data integration, merging data from different sources into one view.

Knowing data integration principles helps design better queries that unify data for analysis.

Merging Lists in Programming

Combining result sets is like merging lists or arrays in programming languages.

Recognizing this similarity helps programmers apply familiar concepts when writing SQL queries.

Common Pitfalls

#1 Trying to combine queries with different numbers of columns.

Wrong approach:

SELECT name FROM customers
UNION
SELECT name, city FROM suppliers;

Correct approach:

SELECT name, city FROM customers
UNION
SELECT name, city FROM suppliers;

Root cause:

Misunderstanding that UNION requires matching column counts and types.

#2 Using UNION when duplicates should be preserved.

Wrong approach:

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

Correct approach:

SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

Root cause:

Not knowing UNION removes duplicates by default.

#3 Assuming UNION orders results automatically.

Wrong approach:

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

Correct approach:

SELECT name FROM customers
UNION
SELECT name FROM suppliers
ORDER BY name;

Root cause:

Believing SQL guarantees order without ORDER BY.

Key Takeaways

Combining result sets merges data from multiple queries into one list, making analysis easier.

UNION removes duplicate rows by default, while UNION ALL keeps all rows including duplicates.

Queries combined with UNION must have the same number of columns with compatible data types.

Use UNION to stack similar data vertically; use JOINs to combine related data horizontally.

Choosing between UNION and UNION ALL affects query performance and result accuracy.