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

Need a 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

Need a 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

Need a 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

Need a hint?

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