Why combining result sets is useful in MySQL - See It in Action

Why Combining Result Sets is Useful

📖 Scenario: You work at a small bookstore that tracks sales in two separate tables: online_sales and store_sales. You want to see all sales together to understand total sales better.

🎯 Goal: Build a combined list of all sales from both online_sales and store_sales tables using SQL UNION to see all sales in one result set.

📋 What You'll Learn

Create two tables: online_sales and store_sales with columns sale_id and amount

Insert exact sales data into both tables

Write a SQL query using UNION to combine sales from both tables

Order the combined results by sale_id

💡 Why This Matters

🌍 Real World

Combining sales data from different sources helps businesses get a complete view of their transactions.

💼 Career

Database professionals often combine data from multiple tables or databases to create reports and insights.

1

Create the sales tables

Create two tables called online_sales and store_sales. Each table should have two columns: sale_id as an integer and amount as a decimal number.

MySQL

-- Create the online_sales and store_sales tables
-- Your code here

Hint

Use CREATE TABLE statements with the exact column names sale_id and amount.

2

Insert sales data

Insert these exact rows into online_sales: (1, 15.50), (2, 23.75). Insert these exact rows into store_sales: (3, 10.00), (4, 5.25).

MySQL

CREATE TABLE online_sales (sale_id INT, amount DECIMAL(10,2));
CREATE TABLE store_sales (sale_id INT, amount DECIMAL(10,2));
-- Insert sales data into online_sales and store_sales
-- Your code here

Hint

Use INSERT INTO with the exact values for each table.

3

Combine sales with UNION

Write a SQL query that uses UNION to combine all rows from online_sales and store_sales. Select sale_id and amount from both tables.

MySQL

CREATE TABLE online_sales (sale_id INT, amount DECIMAL(10,2));
CREATE TABLE store_sales (sale_id INT, amount DECIMAL(10,2));
INSERT INTO online_sales VALUES (1, 15.50), (2, 23.75);
INSERT INTO store_sales VALUES (3, 10.00), (4, 5.25);
-- Write a UNION query to combine sales
-- Your code here

Hint

Use SELECT from each table and combine with UNION.

4

Order the combined results

Add an ORDER BY sale_id clause at the end of your UNION query to sort all sales by sale_id.

MySQL

CREATE TABLE online_sales (sale_id INT, amount DECIMAL(10,2));
CREATE TABLE store_sales (sale_id INT, amount DECIMAL(10,2));
INSERT INTO online_sales VALUES (1, 15.50), (2, 23.75);
INSERT INTO store_sales VALUES (3, 10.00), (4, 5.25);
SELECT sale_id, amount FROM online_sales
UNION
SELECT sale_id, amount FROM store_sales
-- Add ORDER BY sale_id here
-- Your code here

Hint

Place ORDER BY sale_id after the UNION query to sort results.

Practice

1. Why is combining result sets using UNION or UNION ALL useful in MySQL?

easy

A. It allows you to see data from multiple tables together in one list.

B. It speeds up the database server automatically.

C. It deletes duplicate rows from all tables permanently.

D. It creates new tables with combined data.

Solution

1. Step 1: Understand the purpose of combining result sets

   Combining result sets lets you merge rows from different tables or queries into one output.

2. Step 2: Identify the benefit of combined data

   This helps to view or report data together without changing the original tables.

3. Final Answer:

   It allows you to see data from multiple tables together in one list. -> Option A

4. Quick Check:

   Combining result sets = viewing merged data [OK]

Hint: Combining shows data together, not deleting or creating tables [OK]

Common Mistakes:

• Thinking UNION speeds up queries automatically
• Believing UNION deletes data permanently
• Confusing combining with creating new tables

2. Which of the following is the correct syntax to combine two SELECT queries without duplicates in MySQL?

easy

A. SELECT * FROM table1 UNION SELECT * FROM table2;

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

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

D. SELECT * FROM table1 AND SELECT * FROM table2;

Solution

1. Step 1: Recall syntax for combining queries without duplicates

   The UNION keyword combines results and removes duplicates.

2. Step 2: Identify correct syntax

   The correct syntax is SELECT ... UNION SELECT .... UNION ALL keeps duplicates, JOIN and AND are incorrect here.

3. Final Answer:

   SELECT * FROM table1 UNION SELECT * FROM table2; -> Option A

4. Quick Check:

   UNION = combine without duplicates [OK]

Hint: UNION removes duplicates; use UNION ALL to keep them [OK]

Common Mistakes:

• Using JOIN instead of UNION to combine result sets
• Confusing UNION ALL with UNION
• Using AND keyword incorrectly

3. Given two tables:
table1 has rows: (1, 'Apple'), (2, 'Banana')
table2 has rows: (2, 'Banana'), (3, 'Cherry')
What is the result of this query?

SELECT id, name FROM table1 UNION SELECT id, name FROM table2;

medium

A. [(2, 'Banana'), (3, 'Cherry')]

B. [(1, 'Apple'), (2, 'Banana'), (2, 'Banana'), (3, 'Cherry')]

C. [(1, 'Apple'), (2, 'Banana'), (3, 'Cherry')]

D. [(1, 'Apple'), (3, 'Cherry')]

Solution

1. Step 1: Understand UNION removes duplicates

   The row (2, 'Banana') appears in both tables but will appear only once in the result.

2. Step 2: Combine unique rows from both tables

   Rows (1, 'Apple') and (3, 'Cherry') are unique and included as is.

3. Final Answer:

   [(1, 'Apple'), (2, 'Banana'), (3, 'Cherry')] -> Option C

4. Quick Check:

   UNION removes duplicates, so only one (2, 'Banana') [OK]

Hint: UNION removes duplicates; expect unique rows only [OK]

Common Mistakes:

• Expecting duplicate rows to appear twice
• Confusing UNION with UNION ALL output
• Ignoring that UNION sorts and removes duplicates

4. What is wrong with this query if you want to combine all rows from two tables including duplicates?

SELECT * FROM table1 UNION SELECT * FROM table2;

medium

A. It will create a new table instead of showing results.

B. It will cause a syntax error because UNION needs ORDER BY.

C. It will combine rows but only from the first table.

D. It will remove duplicate rows, so duplicates are lost.

Solution

1. Step 1: Understand UNION behavior

   UNION removes duplicate rows from combined results.

2. Step 2: Identify correct way to keep duplicates

   To keep duplicates, use UNION ALL instead of UNION.

3. Final Answer:

   It will remove duplicate rows, so duplicates are lost. -> Option D

4. Quick Check:

   UNION removes duplicates, use UNION ALL to keep them [OK]

Hint: UNION removes duplicates; use UNION ALL to keep duplicates [OK]

Common Mistakes:

• Thinking UNION keeps duplicates
• Believing UNION requires ORDER BY
• Confusing UNION with JOIN behavior

5. You have two tables: sales_2023 and sales_2024. Both have columns product_id and units_sold. You want a report showing all sales from both years including duplicates, but also want to sort by units_sold descending. Which query correctly achieves this?

hard

A. SELECT product_id, units_sold FROM sales_2023 UNION SELECT product_id, units_sold FROM sales_2024 ORDER BY units_sold DESC;

B. SELECT product_id, units_sold FROM sales_2023 UNION ALL SELECT product_id, units_sold FROM sales_2024 ORDER BY 2 DESC;

C. SELECT product_id, units_sold FROM sales_2023 UNION ALL SELECT product_id, units_sold FROM sales_2024 ORDER BY units_sold ASC;

D. SELECT product_id, units_sold FROM sales_2023 ORDER BY units_sold DESC UNION ALL SELECT product_id, units_sold FROM sales_2024;

Solution

1. Step 1: Choose UNION ALL to keep duplicates

   Since duplicates must be included, UNION ALL is needed.

2. Step 2: Apply ORDER BY after the combined query

   ORDER BY must be applied to the entire combined result, so it goes after both SELECTs.

3. Step 3: Use column position or name in ORDER BY

   Using ORDER BY 2 DESC sorts by the second column units_sold descending correctly.

4. Final Answer:

   SELECT product_id, units_sold FROM sales_2023 UNION ALL SELECT product_id, units_sold FROM sales_2024 ORDER BY 2 DESC; -> Option B

5. Quick Check:

   UNION ALL + ORDER BY after combined query = correct [OK]

Hint: Use UNION ALL then ORDER BY after both selects [OK]

Common Mistakes:

• Using UNION instead of UNION ALL loses duplicates
• Placing ORDER BY inside first SELECT only
• Not sorting combined result properly