SQL UNION Operator

The SQL UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. Each query within the UNION must have the same number of columns in the same order, and the columns must have compatible data types.

The UNION operator removes duplicate records by default. To include duplicates, use the UNION ALL operator.

In this tutorial, we will go through SQL UNION Operator, its syntax, and how to use this operator in SQL statements, with the help of well detailed examples.

Syntax of SQL UNION Operator

The basic syntax of the SQL UNION operator is as follows:

</>
Copy
SELECT column1, column2, ...
FROM table1
[WHERE condition]
UNION
SELECT column1, column2, ...
FROM table2
[WHERE condition];

Each part of this syntax has a specific purpose:

  • SELECT column1, column2, …: Specifies the columns to retrieve from each table.
  • FROM table1 and FROM table2: Specifies the tables from which to retrieve data.
  • WHERE condition (optional): Filters rows in each table before applying UNION.
  • UNION: Combines the results of the two queries, removing duplicates by default.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the UNION operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use sample students and alumni tables with fields id, name, age, and locality.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    locality VARCHAR(50)
);

4. Create an alumni table with similar structure:

</>
Copy
CREATE TABLE alumni (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    locality VARCHAR(50)
);

5. Insert sample data into the students and alumni tables:

</>
Copy
INSERT INTO students (name, age, locality)
VALUES
('Alice', 14, 'Northside'),
('Bob', 15, 'Westend'),
('Charlie', 14, 'Northside');

INSERT INTO alumni (name, age, locality)
VALUES
('David', 16, 'Southend'),
('Eva', 15, 'Westend'),
('Alice', 14, 'Northside');