Why combining result sets is useful in MySQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use UNION to combine result sets?

Imagine you have two tables: Customers_US and Customers_EU. You want to get a list of all customers from both tables without duplicates. Why is combining result sets with UNION useful here?

A It duplicates all rows from both tables, including repeated customers.

B It creates a new table combining both tables permanently.

C It only shows customers from the first table and ignores the second.

D It merges rows from both tables and removes duplicates, giving a single list of unique customers.

💡 Hint

Think about how to get a combined list without repeated entries.

UNION combines rows from multiple queries and removes duplicates, which is useful to get a unique list from different sources.

❓ query_result

intermediate

What is the output of this UNION query?

Given two tables:

Table A:
id | name
1 | Alice
2 | Bob

Table B:
id | name
2 | Bob
3 | Carol

What is the result of this query?

SELECT id, name FROM A
UNION
SELECT id, name FROM B;

A 2 | Bob<br>3 | Carol

B 1 | Alice<br>2 | Bob<br>3 | Carol

C 1 | Alice<br>2 | Bob<br>2 | Bob<br>3 | Carol

D 1 | Alice<br>3 | Carol

💡 Hint

Remember that UNION removes duplicates.

The UNION query returns all unique rows from both tables. Since '2 | Bob' appears in both, it shows only once.

📝 Syntax

advanced

Which query correctly combines two SELECTs with UNION ALL?

You want to combine all rows from two tables including duplicates. Which query is correct?

A SELECT name FROM Customers_US UNION ALL SELECT name FROM Customers_EU;

B SELECT name FROM Customers_US UNION SELECT name FROM Customers_EU ALL;

C SELECT name FROM Customers_US ALL UNION SELECT name FROM Customers_EU;

D SELECT name FROM Customers_US UNIONALL SELECT name FROM Customers_EU;

💡 Hint

Check the correct syntax for UNION ALL.

The correct syntax is UNION ALL with a space between UNION and ALL.

❓ optimization

advanced

How does UNION ALL improve performance over UNION?

You have two large tables and want to combine their rows including duplicates. Why might UNION ALL be faster than UNION?

A Because UNION ALL skips the step of removing duplicates, saving time.

B Because UNION ALL creates indexes automatically.

C Because UNION ALL sorts the results faster than UNION.

D Because UNION ALL compresses data before combining.

💡 Hint

Think about what extra work UNION does compared to UNION ALL.

UNION removes duplicates which requires extra processing. UNION ALL just combines all rows directly, so it is faster.

🔧 Debug

expert

Why does this UNION query cause an error?

Consider these two tables:

Employees: id INT, name VARCHAR(50)

Departments: dept_id INT, dept_name VARCHAR(50)

What error will this query cause?

SELECT id, name FROM Employees
UNION
SELECT dept_id FROM Departments;

A Error: UNION cannot combine different data types.

B Error: Column names must match exactly in both SELECTs.

C Error: The number of columns in each SELECT must be the same.

D No error, query runs successfully.

💡 Hint

Check how many columns each SELECT returns.

UNION requires each SELECT to return the same number of columns. Here, the first SELECT returns 2 columns, the second only 1.

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