Why combining result sets is useful in MySQL

Introduction

Combining result sets helps you see data from different tables or queries together in one list. It makes comparing and analyzing data easier.

You want to see all customers and all suppliers in one list.

You need to combine sales data from two different years into one report.

You want to merge lists of products from two stores to check what they both sell.

You want to show all employees from different departments in one table.

You want to combine search results from different categories into one list.

Syntax

MySQL

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

UNION combines results from two queries and removes duplicates.

Use UNION ALL to keep duplicates if needed.

Examples

This shows all unique names from customers and suppliers together.

MySQL

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

This shows all products from both stores, including duplicates.

MySQL

SELECT product FROM store1
UNION ALL
SELECT product FROM store2;

Sample Program

This example creates two tables, adds names, and combines them showing unique names only.

MySQL

CREATE TABLE customers (name VARCHAR(20));
CREATE TABLE suppliers (name VARCHAR(20));

INSERT INTO customers VALUES ('Alice'), ('Bob');
INSERT INTO suppliers VALUES ('Bob'), ('Charlie');

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

Important Notes

The columns in both SELECT statements must match in number and type.

UNION removes duplicates, so repeated rows appear only once.

UNION ALL keeps all rows, including duplicates.

Summary

Combining result sets lets you see data from multiple sources together.

UNION removes duplicate rows; UNION ALL keeps them.

Use this to create reports or lists that merge data easily.

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