Why combining result sets is useful in MySQL - Visual Breakdown

Concept Flow - Why combining result sets is useful

Start with Query 1

↓

Get Result Set 1

↓

Start with Query 2

↓

Get Result Set 2

↓

Combine Result Sets

↓

Final Output

Two or more queries run separately, then their results are combined into one list for easier use.

Execution Sample

MySQL

SELECT name FROM employees WHERE department = 'Sales'
UNION
SELECT name FROM employees WHERE department = 'Marketing';

This query gets names from Sales and Marketing departments and combines them into one list without duplicates.

Execution Table

Step	Query Part	Action	Resulting Rows	Notes
1	SELECT name FROM employees WHERE department = 'Sales'	Run first query	Alice, Bob	Get Sales employees
2	SELECT name FROM employees WHERE department = 'Marketing'	Run second query	Carol, Dave	Get Marketing employees
3	UNION	Combine results	Alice, Bob, Carol, Dave	Duplicates removed if any
4	Final Output	Return combined list	Alice, Bob, Carol, Dave	Single list from both departments

💡 All queries executed and results combined; no more steps.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
ResultSet1	empty	Alice, Bob	Alice, Bob	Alice, Bob	Alice, Bob
ResultSet2	empty	empty	Carol, Dave	Carol, Dave	Carol, Dave
CombinedResult	empty	empty	empty	Alice, Bob, Carol, Dave	Alice, Bob, Carol, Dave

Key Moments - 2 Insights

Why do we run two separate queries before combining results?

Each query targets a specific group (Sales or Marketing). Combining after running both lets us get all needed data in one list, as shown in steps 1 and 2 of the execution_table.

What happens if the same name appears in both departments?

Using UNION removes duplicates automatically, so the final list shows each name only once, as explained in step 3 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what names are in ResultSet1 after Step 1?

A Carol, Dave

B Alice, Bob

C Alice, Bob, Carol, Dave

D Empty

💡 Hint

Check the 'Resulting Rows' column for Step 1 in execution_table.

At which step are the two result sets combined?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for the 'Combine results' action in execution_table.

If we used UNION ALL instead of UNION, what would change in the final output?

A Duplicates would be included

B Duplicates would be removed

C Only Sales employees would show

D Only Marketing employees would show

💡 Hint

UNION ALL keeps duplicates; UNION removes them. See key_moments about duplicates.

Concept Snapshot

Combining result sets lets you get data from multiple queries as one list.
Use UNION to merge and remove duplicates.
Use UNION ALL to merge and keep duplicates.
This helps when data is spread across groups or conditions.
Run queries separately, then combine results for easy use.

Full Transcript

Combining result sets is useful because it lets you gather data from different groups or conditions into one list. For example, you can get employee names from Sales and Marketing departments separately, then combine them using UNION. This removes duplicates so each name appears once. The process runs each query, collects results, then merges them. This way, you get a single list that is easier to work with. Using UNION ALL instead keeps duplicates if needed. This method helps when data is split but you want one combined output.

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