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

Challenge - 5 Problems

❓ query_result

intermediate

Difference in output between UNION and UNION ALL

Consider two tables fruits and more_fruits with the following data:

fruits:       more_fruits:
+-------+    +------------+
| name  |    | name       |
+-------+    +------------+
| apple |    | banana     |
| banana|    | apple      |
| cherry|    | cherry     |
+-------+    +------------+

What will be the result of this query?

SELECT name FROM fruits
UNION
SELECT name FROM more_fruits;

PostgreSQL

SELECT name FROM fruits
UNION
SELECT name FROM more_fruits;

A [{"name": "apple"}, {"name": "banana"}, {"name": "cherry"}]

B [{"name": "apple"}, {"name": "banana"}, {"name": "cherry"}, {"name": "apple"}]

C [{"name": "apple"}, {"name": "banana"}]

D [{"name": "banana"}, {"name": "cherry"}]

💡 Hint

UNION removes duplicates, UNION ALL does not.

The UNION operator combines results from both queries and removes duplicates. Since both tables have the same fruits, the result contains each fruit only once.

❓ query_result

intermediate

Output of UNION ALL with duplicates

Using the same tables fruits and more_fruits as before, what is the output of this query?

SELECT name FROM fruits
UNION ALL
SELECT name FROM more_fruits;

PostgreSQL

SELECT name FROM fruits
UNION ALL
SELECT name FROM more_fruits;

A [{"name": "apple"}, {"name": "banana"}, {"name": "cherry"}]

B [{"name": "apple"}, {"name": "banana"}, {"name": "cherry"}, {"name": "apple"}]

C [{"name": "apple"}, {"name": "banana"}, {"name": "cherry"}, {"name": "banana"}, {"name": "apple"}, {"name": "cherry"}]

D [{"name": "banana"}, {"name": "cherry"}]

💡 Hint

UNION ALL keeps all rows including duplicates.

UNION ALL returns all rows from both queries including duplicates. Since both tables have the same fruits, the output contains each fruit twice.

🧠 Conceptual

advanced

Why use UNION instead of UNION ALL?

Which of the following is the main reason to use UNION instead of UNION ALL?

A To filter rows based on a condition

B To combine results faster without removing duplicates

C To join tables based on matching columns

D To combine results and remove duplicate rows

💡 Hint

Think about what happens to duplicates.

UNION removes duplicate rows from the combined result, while UNION ALL keeps all rows including duplicates.

📝 Syntax

advanced

Identify the syntax error in UNION query

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

A SELECT id, name FROM users UNION ALL SELECT id FROM customers;

B SELECT id, name FROM users UNION SELECT id, name FROM customers;

C SELECT id, name FROM users UNION ALL SELECT id, name FROM customers;

D ;sremotsuc MORF eman ,di TCELES NOINU sresu MORF eman ,di TCELES

💡 Hint

Check if both SELECT statements have the same number of columns.

UNION and UNION ALL require both SELECT statements to have the same number of columns with compatible types. Option A selects 2 columns in the first query but only 1 in the second, causing a syntax error.

❓ optimization

expert

Performance impact of UNION vs UNION ALL

You have two large tables with millions of rows each. You want to combine their data without duplicates. Which query is likely to perform better and why?

Option 1:
SELECT * FROM table1
UNION
SELECT * FROM table2;

Option 2:
SELECT * FROM table1
UNION ALL
SELECT * FROM table2;
-- then remove duplicates in application code

A Option 2 performs better because UNION ALL is faster and removing duplicates in application is cheaper.

B Option 1 performs worse because UNION requires sorting and duplicate elimination.

C Both options perform the same because duplicates removal cost is equal.

D Option 1 performs better because the database removes duplicates efficiently.

💡 Hint

Consider how databases handle UNION internally.

UNION requires sorting or hashing to remove duplicates, which can be expensive on large datasets. UNION ALL simply concatenates results without duplicate checks, so it is faster. However, removing duplicates outside the database may be less efficient overall.