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');
SQL UNION Operator - Setup for examples

Examples: Using UNION Operator in Queries

Now, let’s explore different scenarios of using the UNION operator with these tables.

SQL UNION Operator - Example table data - students
SQL UNION Operator - Example table data - alumni

1. Combining All Students and Alumni Names

To retrieve a list of names from both the students and alumni tables, eliminating duplicates:

</>
Copy
SELECT name FROM students
UNION
SELECT name FROM alumni;

This query returns a unique list of names from both tables, including students and alumni but without duplicates.

SQL UNION Operator Example - Combining All Students and Alumni Names

2. Combining Data and Keeping Duplicates with UNION ALL

To include duplicate names in the combined list:

</>
Copy
SELECT name FROM students
UNION ALL
SELECT name FROM alumni;

This query returns all names from both tables, including any duplicates.

SQL UNION Operator Example - Combining Data and Keeping Duplicates with UNION ALL

3. Combining Tables with Additional WHERE Clauses

To retrieve names of students and alumni from “Westend” locality only:

</>
Copy
SELECT name FROM students
WHERE locality = 'Westend'
UNION
SELECT name FROM alumni
WHERE locality = 'Westend';

This query filters both tables to include only those records where locality is “Westend”, combining results without duplicates.

SQL UNION Operator Example - Combining Tables with Additional WHERE Clauses

Reference: SQL WHERE Clause

4. Combining and Renaming Columns in Results

To retrieve names and ages from both tables, renaming the column as “student_age”:

</>
Copy
SELECT name, age AS student_age FROM students
UNION
SELECT name, age AS student_age FROM alumni;

This query retrieves names and ages from both tables, with the age column consistently labeled as “student_age” in the combined result set.

SQL UNION Operator Example - Combining and Renaming Columns in Results

FAQs for SQL UNION

1. What does the SQL UNION operator do?

The UNION operator combines the results of two or more SELECT queries, returning a unique set of records by default.

2. How is UNION different from UNION ALL?

UNION removes duplicates from the combined result set, while UNION ALL includes all duplicates.

3. Can I use UNION with different columns in each query?

No, each query in a UNION must return the same number of columns in the same order and with compatible data types.

4. Does UNION include NULL values?

Yes, UNION includes NULL values in the result set, treating them as valid entries.

5. Can UNION be used with WHERE conditions?

Yes, you can use WHERE conditions in each individual query before applying the UNION operator.