UNION and UNION ALL in MySQL - Mini Project: Build & Apply

Using UNION and UNION ALL in SQL

📖 Scenario: You work at a small bookstore that keeps separate tables for books in two different genres: Fiction and Non-Fiction. You want to create combined lists of all books for reports.

🎯 Goal: Build SQL queries using UNION and UNION ALL to combine book lists from two tables.

📋 What You'll Learn

Create two tables named FictionBooks and NonFictionBooks with columns BookID and Title.

Insert specific book entries into each table.

Write a query using UNION to combine unique book titles from both tables.

Write a query using UNION ALL to combine all book titles including duplicates.

💡 Why This Matters

🌍 Real World

Combining data from multiple sources or tables is common in reporting and data analysis in businesses.

💼 Career

Understanding UNION and UNION ALL is essential for database querying roles, data analysts, and backend developers who work with SQL databases.

1

Create FictionBooks and NonFictionBooks tables with data

Create two tables called FictionBooks and NonFictionBooks. Each table should have columns BookID (integer) and Title (text). Insert these exact rows into FictionBooks: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird'). Insert these exact rows into NonFictionBooks: (1, 'Sapiens'), (2, 'Educated'), (3, '1984').

MySQL

-- Create tables FictionBooks and NonFictionBooks and insert the given rows
-- Your code here

Hint

Use CREATE TABLE statements for both tables. Then use INSERT INTO with multiple rows for each table.

2

Set up a query to select titles from both tables

Write two separate SELECT queries: one to select the Title column from FictionBooks and one to select the Title column from NonFictionBooks. Assign each query to a variable named fiction_titles and nonfiction_titles respectively (for example, as CTEs or subqueries).

MySQL

CREATE TABLE FictionBooks (BookID INT, Title VARCHAR(100));
CREATE TABLE NonFictionBooks (BookID INT, Title VARCHAR(100));

INSERT INTO FictionBooks (BookID, Title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

INSERT INTO NonFictionBooks (BookID, Title) VALUES
(1, 'Sapiens'),
(2, 'Educated'),
(3, '1984');

-- Write SELECT queries for fiction_titles and nonfiction_titles
-- Your code here

Hint

Use WITH to create named queries (CTEs) for fiction and nonfiction titles.

3

Write a query using UNION to combine unique book titles

Write a SELECT query that combines fiction_titles and nonfiction_titles using UNION to get a list of unique book titles from both genres.

MySQL

CREATE TABLE FictionBooks (BookID INT, Title VARCHAR(100));
CREATE TABLE NonFictionBooks (BookID INT, Title VARCHAR(100));

INSERT INTO FictionBooks (BookID, Title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

INSERT INTO NonFictionBooks (BookID, Title) VALUES
(1, 'Sapiens'),
(2, 'Educated'),
(3, '1984');

WITH fiction_titles AS (
  SELECT Title FROM FictionBooks
), nonfiction_titles AS (
  SELECT Title FROM NonFictionBooks
)

-- Write a SELECT query using UNION to combine unique titles
-- Your code here

Hint

Use UNION between the two SELECT statements to combine unique titles.

4

Write a query using UNION ALL to combine all book titles including duplicates

Write a SELECT query that combines fiction_titles and nonfiction_titles using UNION ALL to get a list of all book titles from both genres, including duplicates.

MySQL

CREATE TABLE FictionBooks (BookID INT, Title VARCHAR(100));
CREATE TABLE NonFictionBooks (BookID INT, Title VARCHAR(100));

INSERT INTO FictionBooks (BookID, Title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird');

INSERT INTO NonFictionBooks (BookID, Title) VALUES
(1, 'Sapiens'),
(2, 'Educated'),
(3, '1984');

WITH fiction_titles AS (
  SELECT Title FROM FictionBooks
), nonfiction_titles AS (
  SELECT Title FROM NonFictionBooks
)

SELECT Title FROM fiction_titles
UNION
SELECT Title FROM nonfiction_titles;

-- Write a SELECT query using UNION ALL to combine all titles including duplicates
-- Your code here

Hint

Use UNION ALL between the two SELECT statements to include duplicates.

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