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.