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.