UNION and UNION ALL in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to combine results from two tables without duplicates.

PostgreSQL

SELECT name FROM employees [1] SELECT name FROM managers;

Drag options to blanks, or click blank then click option'

AUNION

BJOIN

CUNION ALL

DINTERSECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNION ALL when duplicates should be removed.

Using JOIN instead of UNION to combine rows.

The UNION keyword combines results from two queries and removes duplicates.

2fill in blank

medium

Complete the code to combine all rows from two tables including duplicates.

PostgreSQL

SELECT product_id FROM sales_2023 [1] SELECT product_id FROM sales_2024;

Drag options to blanks, or click blank then click option'

AINTERSECT

BUNION ALL

CUNION

DEXCEPT

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNION instead of UNION ALL when duplicates are needed.

Using INTERSECT which returns only common rows.

The UNION ALL keyword combines all rows from both queries including duplicates.

3fill in blank

hard

Fix the error in the query to combine customer names from two tables.

PostgreSQL

SELECT customer_name FROM customers [1] ALL SELECT customer_name FROM clients;

Drag options to blanks, or click blank then click option'

AINTERSECT

BUNION

CJOIN

DUNION ALL

Attempts:

3 left

💡 Hint

Common Mistakes

Writing 'UNION ALL' with extra words or missing space.

Using JOIN instead of UNION ALL.

The correct syntax is UNION ALL without any word between UNION and ALL.

4fill in blank

hard

Fill both blanks to combine two queries and order the results by id.

PostgreSQL

SELECT id FROM table1 [1] SELECT id FROM table2 ORDER BY [2];

Drag options to blanks, or click blank then click option'

AUNION

BUNION ALL

Cid

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNION ALL when duplicates should be removed.

Ordering by a column not in the SELECT list.

Use UNION to combine without duplicates and order by id.

5fill in blank

hard

Fill all three blanks to combine two queries selecting user and score, keeping duplicates, and order by score descending.

PostgreSQL

SELECT user, score FROM scores_2022 [1] SELECT user, score FROM scores_2023 ORDER BY [2] [3];

Drag options to blanks, or click blank then click option'

AUNION

BUNION ALL

Cscore

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNION instead of UNION ALL when duplicates are needed.

Ordering ascending instead of descending.

Use UNION ALL to keep duplicates, order by score in descending order.

Practice

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

easy

A. UNION removes duplicate rows, while UNION ALL keeps all rows including duplicates.

B. UNION requires matching column names, UNION ALL does not.

C. UNION ALL removes duplicates, UNION keeps duplicates.

D. UNION can combine different numbers of columns, UNION ALL cannot.

Solution

1. Step 1: Understand the behavior of UNION

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

2. Step 2: Understand the behavior of UNION ALL

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

3. Final Answer:

   UNION removes duplicates, UNION ALL keeps duplicates. -> Option A

4. Quick Check:

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

Hint: UNION removes duplicates; UNION ALL keeps all rows [OK]

Common Mistakes:

• Thinking UNION ALL removes duplicates
• Believing UNION allows different column counts
• Confusing column name requirements

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

easy

A. SELECT name FROM students UNION ALL SELECT name FROM teachers;

B. SELECT name FROM students UNIONALL SELECT name FROM teachers;

C. SELECT name FROM students UNION ALL FROM teachers;

D. SELECT name FROM students UNION ALL SELECT name FROM teachers WHERE;

Solution

1. Step 1: Check the correct keyword spacing

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

2. Step 2: Verify the SELECT statements are complete

   Both queries must be valid SELECT statements with matching column counts and types.

3. Final Answer:

   SELECT name FROM students UNION ALL SELECT name FROM teachers; -> Option A

4. Quick Check:

   Correct syntax uses 'UNION ALL' with space [OK]

Hint: Use space between UNION and ALL, both queries must be valid [OK]

Common Mistakes:

• Writing UNIONALL as one word
• Missing SELECT in second query
• Adding WHERE without condition

3. Given two tables:
Table A:
id | value
1 | 'apple'
2 | 'banana'

Table B:
id | value
2 | 'banana'
3 | 'cherry'

What is the result of this query?

SELECT value FROM A UNION SELECT value FROM B ORDER BY value;

medium

A. apple, banana, banana, cherry

B. apple, banana, cherry, banana

C. banana, cherry

D. apple, banana, cherry

Solution

1. Step 1: Combine values from both tables using UNION

   UNION removes duplicates, so 'banana' appears only once.

2. Step 2: Order the combined results alphabetically

   Ordering by value gives: 'apple', 'banana', 'cherry'.

3. Final Answer:

   apple, banana, cherry -> Option D

4. Quick Check:

   UNION removes duplicates, so only one 'banana' [OK]

Hint: UNION removes duplicates; check ordering carefully [OK]

Common Mistakes:

• Including duplicate 'banana' with UNION
• Ignoring ORDER BY effect
• Confusing UNION with UNION ALL

4. Consider this query:

SELECT id FROM table1 UNION ALL SELECT id FROM table2;

It returns an error. What is the most likely cause?

medium

A. The tables have different names.

B. The two SELECT statements have different numbers of columns.

C. UNION ALL cannot be used with SELECT statements.

D. The query is missing a semicolon.

Solution

1. Step 1: Check column counts in both SELECT statements

   UNION ALL requires both SELECTs to have the same number of columns.

2. Step 2: Identify error cause

   If column counts differ, PostgreSQL throws an error.

3. Final Answer:

   The two SELECT statements have different numbers of columns. -> Option B

4. Quick Check:

   Column count mismatch causes UNION ALL error [OK]

Hint: Ensure both SELECTs have same column count for UNION ALL [OK]

Common Mistakes:

• Thinking table names must match
• Believing UNION ALL disallows SELECT
• Ignoring column count mismatch

5. You want to combine two queries:

SELECT id, name FROM employees WHERE department = 'Sales'
SELECT id, name FROM contractors WHERE department = 'Sales'

and keep all rows including duplicates. Which query correctly does this and why?

hard

A. SELECT id, name FROM employees UNION ALL SELECT id, name FROM contractors; Because WHERE is not needed in second query.

B. SELECT id, name FROM employees UNION SELECT id, name FROM contractors WHERE department = 'Sales'; Because UNION removes duplicates.

C. SELECT id, name FROM employees UNION ALL SELECT id, name FROM contractors WHERE department = 'Sales'; Because UNION ALL keeps duplicates.

D. SELECT id, name FROM employees UNION SELECT id, name FROM contractors; Because UNION ALL is invalid here.

Solution

1. Step 1: Understand requirement to keep duplicates

   To keep duplicates, use UNION ALL because UNION removes duplicates.

2. Step 2: Check WHERE clause usage

   Both queries filter department = 'Sales', so WHERE must be in both queries.

3. Final Answer:

   SELECT id, name FROM employees UNION ALL SELECT id, name FROM contractors WHERE department = 'Sales'; Because UNION ALL keeps duplicates. -> Option C

4. Quick Check:

   Keep duplicates = UNION ALL with correct WHERE clauses [OK]

Hint: Use UNION ALL to keep duplicates; apply WHERE in each SELECT [OK]

Common Mistakes:

• Using UNION instead of UNION ALL
• Omitting WHERE in second query
• Thinking UNION ALL is invalid