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

Using UNION and UNION ALL in PostgreSQL

📖 Scenario: You work at a bookstore that has two separate tables: one for new arrivals and one for best sellers. You want to create combined lists of books for different purposes.

🎯 Goal: Build SQL queries using UNION and UNION ALL to combine data from two tables: new_arrivals and best_sellers. You will first create the tables, then write queries to combine their data without duplicates and with duplicates.

📋 What You'll Learn

Create two tables named new_arrivals and best_sellers with columns book_id and title.

Insert specific book records into both tables.

Write a query using UNION to combine the two tables without duplicate titles.

Write a query using UNION ALL to combine the two tables including duplicates.

💡 Why This Matters

🌍 Real World

Combining data from multiple sources is common in real-world databases, such as merging product lists, customer records, or event logs.

💼 Career

Understanding how to use UNION and UNION ALL is essential for database querying and reporting tasks in roles like data analyst, database developer, and backend engineer.

1

Create the tables and insert data

Create two tables called new_arrivals and best_sellers with columns book_id (integer) and title (text). Insert these exact rows into new_arrivals: (1, 'The Silent Patient'), (2, 'Where the Crawdads Sing'), (3, 'The Midnight Library'). Insert these exact rows into best_sellers: (10, 'The Midnight Library'), (11, 'Becoming'), (12, 'Educated').

PostgreSQL

-- Create tables new_arrivals and best_sellers and insert the specified rows
-- Your code here

Need a hint?

Use CREATE TABLE statements for both tables. Then use INSERT INTO with the exact values given.

2

Write a UNION query to combine unique book titles

Write a SQL query that selects the title column from new_arrivals and best_sellers and combines them using UNION to show all unique book titles without duplicates.

PostgreSQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
CREATE TABLE best_sellers (book_id INTEGER, title TEXT);

INSERT INTO new_arrivals (book_id, title) VALUES
  (1, 'The Silent Patient'),
  (2, 'Where the Crawdads Sing'),
  (3, 'The Midnight Library');

INSERT INTO best_sellers (book_id, title) VALUES
  (10, 'The Midnight Library'),
  (11, 'Becoming'),
  (12, 'Educated');

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

Need a hint?

Use SELECT title FROM new_arrivals UNION SELECT title FROM best_sellers to combine unique titles.

3

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

Write a SQL query that selects the title column from new_arrivals and best_sellers and combines them using UNION ALL to show all book titles including duplicates.

PostgreSQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
CREATE TABLE best_sellers (book_id INTEGER, title TEXT);

INSERT INTO new_arrivals (book_id, title) VALUES
  (1, 'The Silent Patient'),
  (2, 'Where the Crawdads Sing'),
  (3, 'The Midnight Library');

INSERT INTO best_sellers (book_id, title) VALUES
  (10, 'The Midnight Library'),
  (11, 'Becoming'),
  (12, 'Educated');

SELECT title FROM new_arrivals
UNION
SELECT title FROM best_sellers;

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

Need a hint?

Use UNION ALL to include duplicates from both tables.

4

Add ORDER BY to sort the combined results

Modify the UNION ALL query to add ORDER BY title ASC at the end to sort the combined list of book titles alphabetically.

PostgreSQL

CREATE TABLE new_arrivals (book_id INTEGER, title TEXT);
CREATE TABLE best_sellers (book_id INTEGER, title TEXT);

INSERT INTO new_arrivals (book_id, title) VALUES
  (1, 'The Silent Patient'),
  (2, 'Where the Crawdads Sing'),
  (3, 'The Midnight Library');

INSERT INTO best_sellers (book_id, title) VALUES
  (10, 'The Midnight Library'),
  (11, 'Becoming'),
  (12, 'Educated');

SELECT title FROM new_arrivals
UNION
SELECT title FROM best_sellers;

SELECT title FROM new_arrivals
UNION ALL
SELECT title FROM best_sellers
-- Add ORDER BY clause to sort titles alphabetically
-- Your code here

Need a hint?

Add ORDER BY title ASC after the UNION ALL query to sort the results alphabetically.