UNION combining result sets in SQL - Mini Project: Build & Apply

Combine Customer and Supplier Names Using UNION

📖 Scenario: You work for a company that has two separate tables: one for customers and one for suppliers. Both tables have a column called name that stores the names of people or companies.Your manager wants a single list of all unique names from both tables combined.

🎯 Goal: Create a SQL query that uses UNION to combine the name columns from the customers and suppliers tables into one list without duplicates.

📋 What You'll Learn

Use the customers table with a name column

Use the suppliers table with a name column

Write a SQL query that selects name from both tables

Combine the results using UNION to remove duplicates

Order the final result by name alphabetically

💡 Why This Matters

🌍 Real World

Combining data from different sources to create unified reports or lists is common in business databases.

💼 Career

Knowing how to use UNION helps database professionals merge data cleanly without duplicates, which is essential for accurate reporting.

1

Create the customers table with sample data

Write SQL statements to create a table called customers with a column name of type VARCHAR(50). Insert these exact names into customers: 'Alice', 'Bob', 'Charlie'.

SQL

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

Hint

Use CREATE TABLE customers (name VARCHAR(50)); and then INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Charlie');

2

Create the suppliers table with sample data

Write SQL statements to create a table called suppliers with a column name of type VARCHAR(50). Insert these exact names into suppliers: 'Bob', 'Diana', 'Edward'.

SQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Charlie');
-- Create suppliers table and insert data
-- Your code here

Hint

Use CREATE TABLE suppliers (name VARCHAR(50)); and then INSERT INTO suppliers (name) VALUES ('Bob'), ('Diana'), ('Edward');

3

Write a query to select names from customers

Write a SQL query that selects the name column from the customers table.

SQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Charlie');
CREATE TABLE suppliers (name VARCHAR(50));
INSERT INTO suppliers (name) VALUES ('Bob'), ('Diana'), ('Edward');
-- Select name from customers
-- Your code here

Hint

Use SELECT name FROM customers; to get the names from customers.

4

Combine names from customers and suppliers using UNION

Write a SQL query that selects the name column from customers and combines it with the name column from suppliers using UNION. Order the combined results by name alphabetically.

SQL

CREATE TABLE customers (name VARCHAR(50));
INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Charlie');
CREATE TABLE suppliers (name VARCHAR(50));
INSERT INTO suppliers (name) VALUES ('Bob'), ('Diana'), ('Edward');
-- Combine names from customers and suppliers using UNION and order by name
-- Your code here

Hint

Use SELECT name FROM customers UNION SELECT name FROM suppliers ORDER BY name; to combine and sort names.

Practice

1. What does the SQL UNION operator do when combining results from two SELECT queries?

easy

A. Combines rows but keeps all duplicates

B. Combines rows from both queries and removes duplicate rows

C. Joins tables based on a common column

D. Filters rows based on a condition

Solution

1. Step 1: Understand UNION behavior

   The UNION operator combines rows from two or more SELECT queries into a single result set.

2. Step 2: Check duplicate handling

   By default, UNION removes duplicate rows to ensure unique results.

3. Final Answer:

   Combines rows from both queries and removes duplicate rows -> Option B

4. Quick Check:

   UNION removes duplicates = A [OK]

Hint: UNION removes duplicates by default, unlike UNION ALL [OK]

Common Mistakes:

• Confusing UNION with UNION ALL
• Thinking UNION joins tables by columns
• Assuming UNION filters rows

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

easy

A. SELECT col1 FROM table1 UNION ALL JOIN SELECT col1 FROM table2;

B. SELECT col1 FROM table1 JOIN SELECT col1 FROM table2;

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

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

Solution

1. Step 1: Review correct UNION syntax

   The correct syntax is to write two SELECT statements separated by the UNION keyword.

2. Step 2: Identify invalid options

   Options B, C, and D misuse JOIN or WHERE with UNION, which is incorrect syntax.

3. Final Answer:

   SELECT col1 FROM table1 UNION SELECT col1 FROM table2; -> Option D

4. Quick Check:

   UNION syntax = SELECT ... UNION SELECT ... [OK]

Hint: UNION joins SELECTs directly, no JOIN or WHERE needed [OK]

Common Mistakes:

• Using JOIN instead of UNION
• Placing WHERE before UNION
• Combining UNION ALL with JOIN incorrectly

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 SELECT id FROM B;

medium

A. [1, 2, 3, 4]

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

C. [2, 3]

D. [1, 4]

Solution

1. Step 1: List rows from both tables

   Table A has ids 1, 2, 3; Table B has ids 2, 3, 4.

2. Step 2: Apply UNION behavior

   UNION combines all rows and removes duplicates, so final list is 1, 2, 3, 4.

3. Final Answer:

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

4. Quick Check:

   UNION removes duplicates = [1, 2, 3, 4] [OK]

Hint: UNION removes duplicates, so no repeated ids appear [OK]

Common Mistakes:

• Expecting duplicates to appear
• Confusing UNION with UNION ALL
• Listing only common ids

4. Consider this SQL query:
SELECT name FROM employees UNION SELECT name, department FROM managers;
What is the issue with this query?

medium

A. The number of columns in both SELECT statements differ

B. UNION cannot be used with SELECT statements

C. The query will return duplicate rows

D. The query is missing a WHERE clause

Solution

1. Step 1: Check column counts in SELECTs

   The first SELECT returns 1 column (name), the second returns 2 columns (name, department).

2. Step 2: Understand UNION column rules

   UNION requires all SELECT statements to have the same number of columns with compatible types.

3. Final Answer:

   The number of columns in both SELECT statements differ -> Option A

4. Quick Check:

   UNION needs same columns = C [OK]

Hint: UNION needs same column count in all SELECTs [OK]

Common Mistakes:

• Ignoring column count mismatch
• Thinking UNION filters duplicates only
• Assuming WHERE clause is mandatory

5. You have two tables:
Sales2023(product, amount)
Apple, 100
Banana, 150
Sales2024(product, amount)
Banana, 200
Cherry, 300
Write a query using UNION to list all unique products sold in both years, sorted alphabetically.

hard

A. SELECT product, amount FROM Sales2023 UNION SELECT product, amount FROM Sales2024 ORDER BY amount;

B. SELECT product FROM Sales2023 UNION ALL SELECT product FROM Sales2024 ORDER BY product;

C. SELECT product FROM Sales2023 UNION SELECT product FROM Sales2024 ORDER BY product;

D. SELECT product FROM Sales2023 JOIN Sales2024 ON product ORDER BY product;

Solution

1. Step 1: Select product column from both tables

   We want unique products, so select only the product column from both tables.

2. Step 2: Use UNION to combine and remove duplicates

   UNION combines both lists and removes duplicates, giving unique products.

3. Step 3: Sort results alphabetically

   ORDER BY product sorts the final list alphabetically.

4. Final Answer:

   SELECT product FROM Sales2023 UNION SELECT product FROM Sales2024 ORDER BY product; -> Option C

5. Quick Check:

   UNION + ORDER BY product = B [OK]

Hint: Use UNION to remove duplicates, ORDER BY to sort [OK]

Common Mistakes:

• Using UNION ALL which keeps duplicates
• Selecting amount column when only product needed
• Using JOIN instead of UNION