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.

Practice

1. What is the main difference between UNION and UNION ALL in MySQL?

easy

A. UNION sorts the result, UNION ALL does not.

B. UNION keeps all rows including duplicates, UNION ALL removes duplicates.

C. UNION only works with numeric columns, UNION ALL works with all data types.

D. UNION removes duplicate rows, UNION ALL keeps all rows including duplicates.

Solution

1. Step 1: Understand UNION behavior

   UNION combines results from two queries and removes duplicate rows to return only unique rows.

2. Step 2: Understand UNION ALL behavior

   UNION ALL combines results but keeps all rows, including duplicates, without filtering.

3. Final Answer:

   UNION removes duplicate rows, UNION ALL keeps all rows including duplicates. -> Option D

4. Quick Check:

   Duplicates removed = UNION, duplicates kept = UNION ALL [OK]

Hint: Remember: UNION = unique rows, UNION ALL = all rows [OK]

Common Mistakes:

• Thinking UNION ALL removes duplicates
• Confusing sorting behavior with UNION
• Assuming UNION only works on numeric data

2. Which of the following is the correct syntax to combine two SELECT queries using UNION ALL in MySQL?

easy

A. SELECT * FROM table1 UNION ALL SELECT * FROM table2;

B. SELECT * FROM table1 UNIONALL SELECT * FROM table2;

C. SELECT * FROM table1 UNION, ALL SELECT * FROM table2;

D. SELECT * FROM table1 UNION; ALL SELECT * FROM table2;

Solution

1. Step 1: Check correct keyword spacing

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

2. Step 2: Validate SQL syntax

   The syntax must be: SELECT ... UNION ALL SELECT ...; without commas or semicolons between the two SELECTs.

3. Final Answer:

   SELECT * FROM table1 UNION ALL SELECT * FROM table2; -> Option A

4. Quick Check:

   Correct keyword and syntax = SELECT * FROM table1 UNION ALL SELECT * FROM table2; [OK]

Hint: Use space between UNION and ALL, no commas [OK]

Common Mistakes:

• Writing UNIONALL as one word
• Adding commas between SELECT statements
• Using semicolons between SELECTs

3. Given two tables:
Table A:
id | name
1 | Alice
2 | Bob

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

What will be the result of this query?

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

medium

A. 2 | Bob3 | Carol

B. 1 | Alice2 | Bob2 | Bob3 | Carol

C. 1 | Alice2 | Bob3 | Carol

D. 1 | Alice3 | Carol

Solution

1. Step 1: Combine rows from both tables

   Table A has (1, Alice) and (2, Bob). Table B has (2, Bob) and (3, Carol).

2. Step 2: Apply UNION behavior

   UNION removes duplicates, so (2, Bob) appears only once in the result.

3. Final Answer:

   1 | Alice
   2 | Bob
   3 | Carol -> Option C

4. Quick Check:

   Duplicates removed, unique rows shown [OK]

Hint: UNION removes duplicates, so repeated rows appear once [OK]

Common Mistakes:

• Including duplicate rows with UNION
• Confusing UNION with UNION ALL output
• Ignoring duplicate row removal

4. Consider this query:

SELECT id FROM table1
UNION ALL
SELECT id FROM table2 ORDER BY id;

What is wrong with this query?

medium

A. ORDER BY must be applied only once at the end of the UNION ALL query.

B. UNION ALL cannot be used with ORDER BY.

C. Each SELECT must have its own ORDER BY clause.

D. ORDER BY should come before UNION ALL.

Solution

1. Step 1: Understand ORDER BY with UNION ALL

   ORDER BY applies to the entire combined result, not to individual SELECTs.

2. Step 2: Identify correct placement

   ORDER BY must be placed after the last SELECT and UNION ALL, not inside one SELECT.

3. Final Answer:

   ORDER BY must be applied only once at the end of the UNION ALL query. -> Option A

4. Quick Check:

   ORDER BY after UNION ALL, not inside SELECT [OK]

Hint: Place ORDER BY after all UNION ALL parts [OK]

Common Mistakes:

• Putting ORDER BY inside one SELECT only
• Trying ORDER BY before UNION ALL
• Assuming UNION ALL disallows ORDER BY

5. You have two tables:
Employees:
id | name
1 | John
2 | Jane
3 | Mike

Contractors:
id | name
2 | Jane
4 | Anna

You want to list all people from both tables, including duplicates, and sort by name ascending.
Which query achieves this correctly?

hard

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

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

C. SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY id ASC;

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

Solution

1. Step 1: Choose UNION ALL to keep duplicates

   Since duplicates like Jane (id 2) should appear twice, use UNION ALL.

2. Step 2: Apply ORDER BY after UNION ALL

   ORDER BY must come after the full UNION ALL to sort combined results by name ascending.

3. Final Answer:

   SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC; -> Option B

4. Quick Check:

   UNION ALL + ORDER BY at end = SELECT id, name FROM Employees UNION ALL SELECT id, name FROM Contractors ORDER BY name ASC; [OK]

Hint: Use UNION ALL for duplicates, ORDER BY after all SELECTs [OK]

Common Mistakes:

• Using UNION instead of UNION ALL (removes duplicates)
• Placing ORDER BY inside one SELECT
• Incorrect syntax with ORDER BY placement