MySQL UNION Operator

Introduction

In this chapter, we will learn about the UNION operator in MySQL. The UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements, while UNION ALL retains all rows, including duplicates. This is useful for consolidating similar data from different tables or queries into a single result set. We will cover the syntax, examples, and important considerations for using the UNION operator.

Syntax

The basic syntax for the UNION operator is:

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

For retaining duplicate rows, use UNION ALL:

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

• column1, column2, ...: The columns to be selected. The columns in all SELECT statements must have the same data types and be in the same order.
• table1, table2, ...: The tables from which to select data.

Using UNION Operator

Example with UNION

SELECT first_name, last_name, email
FROM students
UNION
SELECT first_name, last_name, email
FROM alumni;

This example retrieves unique rows from both the students and alumni tables, combining them into a single result set.

Example with UNION ALL

SELECT first_name, last_name, email
FROM students
UNION ALL
SELECT first_name, last_name, email
FROM alumni;

This example retrieves all rows from both the students and alumni tables, including duplicates.

Full Example

Let’s go through a full example where we create tables, insert data into them, and use the UNION operator to combine records.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create Tables:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

CREATE TABLE alumni (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    graduation_date DATE
);

4. Insert Data into the Tables:

INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02');

INSERT INTO alumni (first_name, last_name, email, graduation_date) VALUES
('Amit', 'Kumar', 'amit.kumar@example.com', '2020-07-01'),
('Neha', 'Verma', 'neha.verma@example.com', '2019-07-02'),
('Priya', 'Singh', 'priya.singh@example.com', '2021-07-02');

5. Use the UNION Operator:

SELECT first_name, last_name, email
FROM students
UNION
SELECT first_name, last_name, email
FROM alumni;

Output

6. Use the UNION ALL Operator:

SELECT first_name, last_name, email
FROM students
UNION ALL
SELECT first_name, last_name, email
FROM alumni;

Output

Important Considerations

• Column Data Types: The columns selected in each SELECT statement must have the same data types and appear in the same order.
• Performance: Using UNION can impact performance, especially with large datasets. Use UNION ALL if you do not need to eliminate duplicates, as it is faster.
• Sorting: To sort the combined result set, use the ORDER BY clause at the end of the last SELECT statement.

Example with ORDER BY

SELECT first_name, last_name, email
FROM students
UNION
SELECT first_name, last_name, email
FROM alumni
ORDER BY last_name;

Conclusion

The UNION operator is used for combining the result sets of multiple SELECT statements in MySQL. This chapter covered how to use the UNION and UNION ALL operators, provided examples with SELECT statements, and discussed important considerations.