UNION and UNION ALL in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Difference in row count between UNION and UNION ALL

Consider two tables, Employees and Contractors, each with a column name.
What is the number of rows returned by the following queries?

SELECT name FROM Employees UNION SELECT name FROM Contractors;
and

SELECT name FROM Employees UNION ALL SELECT name FROM Contractors;

Assume Employees has 3 rows: Alice, Bob, Charlie and Contractors has 3 rows: Bob, Dave, Eve.

A UNION returns 5 rows; UNION ALL returns 6 rows

B UNION returns 6 rows; UNION ALL returns 5 rows

C Both UNION and UNION ALL return 6 rows

D Both UNION and UNION ALL return 5 rows

💡 Hint

UNION removes duplicates, UNION ALL does not.

UNION combines rows and removes duplicates, so Bob appears once. UNION ALL combines all rows including duplicates.

🧠 Conceptual

intermediate

Behavior of UNION with different column names

Given two tables:
Table1(id INT, name VARCHAR(10))
Table2(user_id INT, username VARCHAR(10))

Which statement about the query below is true?

SELECT id, name FROM Table1 UNION SELECT user_id, username FROM Table2;

A The query fails because column names differ between the two SELECT statements.

B The query returns only rows where id equals user_id and name equals username.

C The query runs successfully and returns combined rows based on column positions, ignoring column names.

D The query returns an error because UNION requires identical table names.

💡 Hint

UNION matches columns by position, not by name.

UNION combines results by matching columns in order, not by their names. Different column names do not cause errors.

📝 Syntax

advanced

Identify the syntax error in UNION query

Which of the following UNION queries will cause a syntax error in MySQL?

A SELECT id, name FROM Employees UNION SELECT id, name FROM Contractors ORDER BY name;

B SELECT id, name FROM Employees ORDER BY name UNION SELECT id, name FROM Contractors;

C SELECT id, name FROM Employees UNION SELECT id, name FROM Contractors;

D SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name;

💡 Hint

ORDER BY must be at the end of the entire UNION query.

ORDER BY inside the first SELECT before UNION is invalid syntax. ORDER BY applies to the final combined result.

❓ optimization

advanced

Performance impact of UNION vs UNION ALL

You have two large tables with millions of rows each. You want to combine their rows including duplicates. Which query is more efficient and why?

A Using UNION is more efficient because it removes duplicates during the merge.

B Using UNION ALL is less efficient because it sorts the results.

C Both UNION and UNION ALL have the same performance for large tables.

D Using UNION ALL is more efficient because it simply concatenates rows without checking duplicates.

💡 Hint

Removing duplicates requires extra work.

UNION removes duplicates which requires sorting or hashing, making it slower. UNION ALL just concatenates rows, so it is faster.

🔧 Debug

expert

Why does this UNION query fail?

Given the query:

SELECT id, name FROM Employees UNION SELECT id FROM Contractors;

Why does this query fail?

A Because the number of columns in the two SELECT statements differ.

B Because UNION requires the same table in both SELECT statements.

C Because the column names must be identical in both SELECT statements.

D Because UNION cannot be used with SELECT statements that have WHERE clauses.

💡 Hint

UNION requires the same number of columns in each SELECT.

UNION requires each SELECT to have the same number of columns with compatible types. Here, the second SELECT has only one column.