UNION ALL with duplicates in SQL - Mini Project: Build & Apply

Using UNION ALL to Combine Tables with Duplicates

📖 Scenario: You work at a bookstore that has two separate tables for new arrivals and best sellers. Some books appear in both tables because they are both new and popular.You want to create a combined list of all books from both tables, including duplicates, to analyze total stock.

🎯 Goal: Build a SQL query that uses UNION ALL to combine the new_arrivals and best_sellers tables, keeping duplicate book entries.

📋 What You'll Learn

Create a table called new_arrivals with columns book_id and title and insert 3 specific books.

Create a table called best_sellers with columns book_id and title and insert 3 specific books, including one that duplicates a book from new_arrivals.

Write a SQL query using UNION ALL to combine all rows from both tables, including duplicates.

Ensure the final query selects book_id and title from the combined result.

💡 Why This Matters

🌍 Real World

Combining data from multiple sources like sales and inventory lists to get a full picture including duplicates.

💼 Career

Understanding <code>UNION ALL</code> is important for data analysts and database developers who merge datasets without losing repeated entries.

1

Create the new_arrivals table and insert data

Create a table called new_arrivals with columns book_id (integer) and title (text). Insert these exact rows: (1, 'The Silent Patient'), (2, 'Where the Crawdads Sing'), (3, 'Becoming').

SQL

-- Create new_arrivals table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the best_sellers table and insert data

Create a table called best_sellers with columns book_id (integer) and title (text). Insert these exact rows: (2, 'Where the Crawdads Sing'), (4, 'Educated'), (5, 'The Testaments').

SQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
INSERT INTO new_arrivals (book_id, title) VALUES
(1, 'The Silent Patient'),
(2, 'Where the Crawdads Sing'),
(3, 'Becoming');

-- Create best_sellers table and insert data
-- Your code here

Hint

Remember to use the same column names and data types as in new_arrivals.

3

Write the UNION ALL query to combine both tables

Write a SQL query that selects book_id and title from new_arrivals and combines it with all rows from best_sellers using UNION ALL to keep duplicates.

SQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
INSERT INTO new_arrivals (book_id, title) VALUES
(1, 'The Silent Patient'),
(2, 'Where the Crawdads Sing'),
(3, 'Becoming');

CREATE TABLE best_sellers (book_id INTEGER, title TEXT);
INSERT INTO best_sellers (book_id, title) VALUES
(2, 'Where the Crawdads Sing'),
(4, 'Educated'),
(5, 'The Testaments');

-- Write UNION ALL query below
-- Your code here

Hint

Use UNION ALL between two SELECT statements to keep duplicates.

4

Complete the query with ordering by book_id

Add an ORDER BY book_id clause at the end of your UNION ALL query to sort the combined results by book_id in ascending order.

SQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
INSERT INTO new_arrivals (book_id, title) VALUES
(1, 'The Silent Patient'),
(2, 'Where the Crawdads Sing'),
(3, 'Becoming');

CREATE TABLE best_sellers (book_id INTEGER, title TEXT);
INSERT INTO best_sellers (book_id, title) VALUES
(2, 'Where the Crawdads Sing'),
(4, 'Educated'),
(5, 'The Testaments');

SELECT book_id, title FROM new_arrivals
UNION ALL
SELECT book_id, title FROM best_sellers
-- Add ORDER BY clause below
-- Your code here

Hint

Place ORDER BY book_id at the end of the combined query to sort results.

Practice

1. What does the SQL statement UNION ALL do when combining results from two queries?

easy

A. It combines rows but removes duplicates.

B. It combines all rows from both queries including duplicates.

C. It only returns rows that appear in both queries.

D. It returns rows only from the first query.

Solution

1. Step 1: Understand UNION ALL behavior

   UNION ALL combines results from two queries and keeps all rows, including duplicates.

2. Step 2: Compare with UNION

   Unlike UNION, UNION ALL does not remove duplicate rows.

3. Final Answer:

   It combines all rows from both queries including duplicates. -> Option B

4. Quick Check:

   UNION ALL keeps duplicates = A [OK]

Hint: UNION ALL keeps duplicates, UNION removes them [OK]

Common Mistakes:

• Confusing UNION ALL with UNION
• Thinking duplicates are removed
• Assuming it returns only unique rows

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

easy

A. SELECT col1 FROM table1 UNIONALL SELECT col1 FROM table2;

B. SELECT col1 FROM table1 UNION ALL FROM table2;

C. SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2;

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

Solution

1. Step 1: Check correct UNION ALL syntax

   The correct syntax is to write two SELECT statements separated by 'UNION ALL'.

2. Step 2: Identify syntax errors in options

   SELECT col1 FROM table1 UNIONALL SELECT col1 FROM table2; writes UNIONALL as one word (missing space), SELECT col1 FROM table1 UNION ALL FROM table2; misses the second SELECT keyword, SELECT col1 FROM table1 UNION ALL SELECT FROM table2; misses columns after the second SELECT. SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2; uses correct syntax.

3. Final Answer:

   SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2; -> Option C

4. Quick Check:

   Correct UNION ALL syntax = B [OK]

Hint: UNION ALL needs space and two SELECTs [OK]

Common Mistakes:

• Writing UNIONALL as one word
• Missing SELECT in second query
• Incorrect keyword order

3. Given two tables:
Table A: id
1
2
3
Table B: id
2
3
4
What is the result of:
SELECT id FROM A UNION ALL SELECT id FROM B;

medium

A. [1, 2, 3, 2, 3, 4]

B. [1, 2, 3, 4]

C. [1, 2, 3]

D. [2, 3, 4]

Solution

1. Step 1: List rows from first query

   SELECT id FROM A returns [1, 2, 3].

2. Step 2: List rows from second query

   SELECT id FROM B returns [2, 3, 4].

3. Step 3: Combine results with UNION ALL

   UNION ALL keeps duplicates, so combined list is [1, 2, 3, 2, 3, 4].

4. Final Answer:

   [1, 2, 3, 2, 3, 4] -> Option A

5. Quick Check:

   UNION ALL keeps duplicates = A [OK]

Hint: UNION ALL stacks all rows, duplicates included [OK]

Common Mistakes:

• Removing duplicates like UNION
• Listing only unique values
• Ignoring order of combined rows

4. Consider this SQL query:
SELECT name FROM employees UNION ALL SELECT name FROM departments;
It returns an error. What is the most likely cause?

medium

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

B. UNION ALL cannot be used with SELECT statements.

C. The keyword ALL is not allowed after UNION.

D. The tables employees and departments must have the same name.

Solution

1. Step 1: Check column counts in both SELECTs

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

2. Step 2: Identify error cause

   If employees and departments tables have different columns selected, error occurs.

3. Final Answer:

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

4. Quick Check:

   Column count mismatch causes error = C [OK]

Hint: Both SELECTs must have same columns count [OK]

Common Mistakes:

• Thinking UNION ALL disallows duplicates only
• Assuming table names must match
• Believing ALL keyword is invalid

5. You have two tables:
Sales_2023: product_id, quantity_sold
Sales_2024: product_id, quantity_sold
You want to create a combined list of all sales including duplicates for analysis. Which query correctly uses UNION ALL and also adds a column year to identify the source year?

hard

A. SELECT product_id, quantity_sold, 'year' FROM Sales_2023 UNION ALL SELECT product_id, quantity_sold, 'year' FROM Sales_2024;

B. SELECT product_id, quantity_sold FROM Sales_2023 UNION ALL SELECT product_id, quantity_sold, '2024' AS year FROM Sales_2024;

C. SELECT product_id, quantity_sold, year FROM Sales_2023 UNION ALL SELECT product_id, quantity_sold, year FROM Sales_2024;

D. SELECT product_id, quantity_sold, '2023' AS year FROM Sales_2023 UNION ALL SELECT product_id, quantity_sold, '2024' AS year FROM Sales_2024;

Solution

1. Step 1: Add year column with literal values

   Use '2023' and '2024' as string literals to add a year column in each SELECT.

2. Step 2: Ensure both SELECTs have same columns

   Both SELECTs must have product_id, quantity_sold, and year columns for UNION ALL.

3. Step 3: Combine with UNION ALL

   SELECT product_id, quantity_sold, '2023' AS year FROM Sales_2023 UNION ALL SELECT product_id, quantity_sold, '2024' AS year FROM Sales_2024; correctly combines both tables with year column and keeps duplicates.

4. Final Answer:

   SELECT product_id, quantity_sold, '2023' AS year FROM Sales_2023 UNION ALL SELECT product_id, quantity_sold, '2024' AS year FROM Sales_2024; -> Option D

5. Quick Check:

   Matching columns with year literals + UNION ALL = D [OK]

Hint: Add year as literal in both SELECTs for UNION ALL [OK]

Common Mistakes:

• Mismatched columns count in SELECTs
• Using column name 'year' without value
• Forgetting to alias literals as year