UNION combining result sets in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this UNION query?

Consider two tables:

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

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

What rows will this query return?

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

A 2 | Bob<br>3 | Carol

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

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

D 1 | Alice<br>3 | Carol

💡 Hint

UNION removes duplicate rows from the combined result.

UNION combines rows from both tables but removes duplicates. Since '2 | Bob' appears in both, it shows only once.

📝 Syntax

intermediate

Which UNION query is syntactically correct?

Choose the correct SQL UNION syntax from the options below:

A SELECT id, name FROM A UNION SELECT id, name FROM B ORDER BY name LIMIT 5;

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

C SELECT id, name FROM A UNION SELECT id, name FROM B ORDER BY name;

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

💡 Hint

UNION combines result sets; ORDER BY applies to the whole UNION result and must be placed at the end.

Option D is the basic correct syntax for UNION. Options C and D have ORDER BY after the second SELECT without parentheses, which is invalid. Option D uses UNION ALL, which is valid syntax but different from UNION.

❓ optimization

advanced

Which UNION query is more efficient for combining two large tables without duplicates?

Given two large tables A and B with the same columns, which query is best to combine all unique rows?

A SELECT * FROM A UNION SELECT * FROM B;

B SELECT * FROM A UNION ALL SELECT * FROM B;

C SELECT DISTINCT * FROM (SELECT * FROM A UNION ALL SELECT * FROM B) AS combined;

D SELECT * FROM A INTERSECT SELECT * FROM B;

💡 Hint

UNION removes duplicates automatically, UNION ALL does not.

Option A uses UNION which removes duplicates efficiently. Option A returns duplicates. Option A does UNION ALL then DISTINCT which is less efficient. Option A returns only rows common to both tables.

🔧 Debug

advanced

Why does this UNION query cause an error?

Given tables A(id INT, name VARCHAR) and B(id INT, age INT), why does this query fail?

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

A Because the column data types in the same position differ.

B Because table B does not have a 'name' column.

C Because UNION requires ORDER BY clause.

D Because the number of columns in both SELECTs differ.

💡 Hint

UNION requires matching column types in order.

The first SELECT returns (id INT, name VARCHAR), the second returns (id INT, age INT). The second column types differ (VARCHAR vs INT), causing a type mismatch error.

🧠 Conceptual

expert

What is the difference between UNION and UNION ALL?

Choose the statement that correctly explains the difference between UNION and UNION ALL:

A UNION and UNION ALL behave the same but UNION ALL requires ORDER BY.

B UNION removes duplicate rows from the combined result; UNION ALL includes all rows, duplicates included.

C UNION is faster than UNION ALL because it skips duplicates.

D UNION includes all rows, duplicates included; UNION ALL removes duplicates.

💡 Hint

Think about how duplicates are handled in each.

UNION removes duplicates, so the result has unique rows. UNION ALL returns all rows from both queries, including duplicates. UNION ALL is usually faster because it does not check for duplicates.