UNION ALL with duplicates in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UNION ALL with duplicate rows

Consider two tables Employees_A and Employees_B with the following data:

Employees_A
id | name
1 | Alice
2 | Bob

Employees_B
id | name
2 | Bob
3 | Charlie

What is the result of the following query?

SELECT id, name FROM Employees_A
UNION ALL
SELECT id, name FROM Employees_B;

A

1 | Alice
2 | Bob
3 | Charlie
2 | Bob

B

1 | Alice
2 | Bob
3 | Charlie

C

1 | Alice
3 | Charlie

D

1 | Alice
2 | Bob
2 | Bob
3 | Charlie

💡 Hint

UNION ALL includes all rows from both queries, including duplicates.

UNION ALL returns all rows from both tables without removing duplicates. Since '2 | Bob' appears in both tables, it appears twice in the result.

❓ query_result

intermediate

Counting rows after UNION ALL

Given two tables Orders_2023 and Orders_2024 each with 5 rows, where 2 rows are identical in both tables, what is the total number of rows returned by this query?

SELECT * FROM Orders_2023
UNION ALL
SELECT * FROM Orders_2024;

A 10

B 8

C 5

D 7

💡 Hint

UNION ALL does not remove duplicates, so all rows from both tables are included.

UNION ALL returns all rows from both tables, so total rows = 5 + 5 = 10, even if some rows are duplicates.

📝 Syntax

advanced

Identify the syntax error in UNION ALL query

Which of the following SQL queries will cause a syntax error?

A SELECT id, name FROM Customers UNION ALL SELECT id, name FROM Clients ORDER BY name;

B SELECT id, name FROM Customers UNION ALL SELECT id, name FROM Clients;

C SELECT id, name FROM Customers UNION ALL SELECT id FROM Clients;

D SELECT id, name FROM Customers UNION ALL SELECT id, name FROM Clients WHERE active = 1;

💡 Hint

UNION ALL requires the same number of columns in both SELECT statements.

Option C selects two columns in the first query but only one in the second, causing a syntax error.

❓ optimization

advanced

Performance impact of UNION ALL vs UNION

Which statement about performance is true when comparing UNION ALL and UNION?

A UNION ALL is slower because it removes duplicates.

B UNION ALL is faster because it does not remove duplicates.

C UNION and UNION ALL have the same performance.

D UNION ALL requires sorting the result set.

💡 Hint

Removing duplicates requires extra work.

UNION removes duplicates which requires sorting or hashing, making it slower than UNION ALL which simply concatenates results.

🧠 Conceptual

expert

Effect of UNION ALL on duplicate rows with NULL values

Consider two tables Table1 and Table2 each having a row with values (NULL, 'X'). What will be the result of this query?

SELECT col1, col2 FROM Table1
UNION ALL
SELECT col1, col2 FROM Table2;

Specifically, how many rows with (NULL, 'X') will appear in the result?

A 2 rows

B 0 rows

C 1 row

D Depends on the database settings

💡 Hint

UNION ALL includes all rows even if they contain NULLs and duplicates.

UNION ALL does not remove duplicates, so both rows with (NULL, 'X') appear in the result.