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.

Practice

1. Why is combining result sets using UNION or UNION ALL useful in MySQL?

easy

A. It allows you to see data from multiple tables together in one list.

B. It speeds up the database server automatically.

C. It deletes duplicate rows from all tables permanently.

D. It creates new tables with combined data.

Solution

1. Step 1: Understand the purpose of combining result sets

   Combining result sets lets you merge rows from different tables or queries into one output.

2. Step 2: Identify the benefit of combined data

   This helps to view or report data together without changing the original tables.

3. Final Answer:

   It allows you to see data from multiple tables together in one list. -> Option A

4. Quick Check:

   Combining result sets = viewing merged data [OK]

Hint: Combining shows data together, not deleting or creating tables [OK]

Common Mistakes:

• Thinking UNION speeds up queries automatically
• Believing UNION deletes data permanently
• Confusing combining with creating new tables

2. Which of the following is the correct syntax to combine two SELECT queries without duplicates in MySQL?

easy

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

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

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

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

Solution

1. Step 1: Recall syntax for combining queries without duplicates

   The UNION keyword combines results and removes duplicates.

2. Step 2: Identify correct syntax

   The correct syntax is SELECT ... UNION SELECT .... UNION ALL keeps duplicates, JOIN and AND are incorrect here.

3. Final Answer:

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

4. Quick Check:

   UNION = combine without duplicates [OK]

Hint: UNION removes duplicates; use UNION ALL to keep them [OK]

Common Mistakes:

• Using JOIN instead of UNION to combine result sets
• Confusing UNION ALL with UNION
• Using AND keyword incorrectly

3. Given two tables:
table1 has rows: (1, 'Apple'), (2, 'Banana')
table2 has rows: (2, 'Banana'), (3, 'Cherry')
What is the result of this query?

SELECT id, name FROM table1 UNION SELECT id, name FROM table2;

medium

A. [(2, 'Banana'), (3, 'Cherry')]

B. [(1, 'Apple'), (2, 'Banana'), (2, 'Banana'), (3, 'Cherry')]

C. [(1, 'Apple'), (2, 'Banana'), (3, 'Cherry')]

D. [(1, 'Apple'), (3, 'Cherry')]

Solution

1. Step 1: Understand UNION removes duplicates

   The row (2, 'Banana') appears in both tables but will appear only once in the result.

2. Step 2: Combine unique rows from both tables

   Rows (1, 'Apple') and (3, 'Cherry') are unique and included as is.

3. Final Answer:

   [(1, 'Apple'), (2, 'Banana'), (3, 'Cherry')] -> Option C

4. Quick Check:

   UNION removes duplicates, so only one (2, 'Banana') [OK]

Hint: UNION removes duplicates; expect unique rows only [OK]

Common Mistakes:

• Expecting duplicate rows to appear twice
• Confusing UNION with UNION ALL output
• Ignoring that UNION sorts and removes duplicates

4. What is wrong with this query if you want to combine all rows from two tables including duplicates?

SELECT * FROM table1 UNION SELECT * FROM table2;

medium

A. It will create a new table instead of showing results.

B. It will cause a syntax error because UNION needs ORDER BY.

C. It will combine rows but only from the first table.

D. It will remove duplicate rows, so duplicates are lost.

Solution

1. Step 1: Understand UNION behavior

   UNION removes duplicate rows from combined results.

2. Step 2: Identify correct way to keep duplicates

   To keep duplicates, use UNION ALL instead of UNION.

3. Final Answer:

   It will remove duplicate rows, so duplicates are lost. -> Option D

4. Quick Check:

   UNION removes duplicates, use UNION ALL to keep them [OK]

Hint: UNION removes duplicates; use UNION ALL to keep duplicates [OK]

Common Mistakes:

• Thinking UNION keeps duplicates
• Believing UNION requires ORDER BY
• Confusing UNION with JOIN behavior

5. You have two tables: sales_2023 and sales_2024. Both have columns product_id and units_sold. You want a report showing all sales from both years including duplicates, but also want to sort by units_sold descending. Which query correctly achieves this?

hard

A. SELECT product_id, units_sold FROM sales_2023 UNION SELECT product_id, units_sold FROM sales_2024 ORDER BY units_sold DESC;

B. SELECT product_id, units_sold FROM sales_2023 UNION ALL SELECT product_id, units_sold FROM sales_2024 ORDER BY 2 DESC;

C. SELECT product_id, units_sold FROM sales_2023 UNION ALL SELECT product_id, units_sold FROM sales_2024 ORDER BY units_sold ASC;

D. SELECT product_id, units_sold FROM sales_2023 ORDER BY units_sold DESC UNION ALL SELECT product_id, units_sold FROM sales_2024;

Solution

1. Step 1: Choose UNION ALL to keep duplicates

   Since duplicates must be included, UNION ALL is needed.

2. Step 2: Apply ORDER BY after the combined query

   ORDER BY must be applied to the entire combined result, so it goes after both SELECTs.

3. Step 3: Use column position or name in ORDER BY

   Using ORDER BY 2 DESC sorts by the second column units_sold descending correctly.

4. Final Answer:

   SELECT product_id, units_sold FROM sales_2023 UNION ALL SELECT product_id, units_sold FROM sales_2024 ORDER BY 2 DESC; -> Option B

5. Quick Check:

   UNION ALL + ORDER BY after combined query = correct [OK]

Hint: Use UNION ALL then ORDER BY after both selects [OK]

Common Mistakes:

• Using UNION instead of UNION ALL loses duplicates
• Placing ORDER BY inside first SELECT only
• Not sorting combined result properly