SQL UNION Operator

Introduction

In this chapter, we will learn how to use the UNION operator in SQL. The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the UNION operator effectively.

What is the UNION Operator?

The UNION operator is used to combine the results of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types. The UNION operator removes duplicate records by default.

Syntax for UNION

Basic Syntax

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

• column1, column2, ...: The columns you want to retrieve.
• table1, table2, ...: The names of the tables from which you want to retrieve data.

Example

Assume we have two tables named employees and contractors:

SELECT first_name, last_name, email
FROM employees
UNION
SELECT first_name, last_name, email
FROM contractors;

This command retrieves the first_name, last_name, and email columns from both the employees and contractors tables and combines them into a single result set, removing any duplicate records.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and contractors.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE contractors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hourly_rate DECIMAL(10, 2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000);

INSERT INTO contractors (first_name, last_name, email, hourly_rate)
VALUES
('Priya', 'Sharma', 'priya.sharma@example.com', 40),
('Ravi', 'Verma', 'ravi.verma@example.com', 35),
('Sita', 'Patel', 'sita.patel@example.com', 45);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

3. Using the UNION Operator

To retrieve combined first_name, last_name, and email from both employees and contractors:

SELECT first_name, last_name, email
FROM employees
UNION
SELECT first_name, last_name, email
FROM contractors;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Ravi	Verma	ravi.verma@example.com

4. Using UNION ALL

The UNION ALL operator is used to combine the results of two or more SELECT statements, including duplicate records.

Example

To retrieve combined first_name, last_name, and email from both employees and contractors, including duplicates:

SELECT first_name, last_name, email
FROM employees
UNION ALL
SELECT first_name, last_name, email
FROM contractors;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Ravi	Verma	ravi.verma@example.com
Sita	Patel	sita.patel@example.com

5. Using UNION with Different Columns

To use the UNION operator, the number of columns and their data types must be the same. You can use aliases to make sure the columns match.

Example

To retrieve first_name, last_name, and a calculated payment column from both employees and contractors:

SELECT first_name, last_name, salary AS payment
FROM employees
UNION
SELECT first_name, last_name, hourly_rate * 40 * 4 AS payment
FROM contractors;

Output

first_name	last_name	payment
Ramesh	Kumar	50000.00
Sita	Patel	60000.00
Arjun	Singh	55000.00
Priya	Sharma	6400.00
Ravi	Verma	5600.00
Sita	Patel	7200.00

Using ORDER BY with UNION

You can use the ORDER BY clause to sort the combined result set. The ORDER BY clause should be placed at the end of the last SELECT statement.

Example

To retrieve combined first_name, last_name, and email from both employees and contractors and sort by last_name:

SELECT first_name, last_name, email
FROM employees
UNION
SELECT first_name, last_name, email
FROM contractors
ORDER BY last_name;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Arjun	Singh	arjun.singh@example.com
Sita	Patel	sita.patel@example.com
Priya	Sharma	priya.sharma@example.com
Ravi	Verma	ravi.verma@example.com

Conclusion

The UNION operator is used for combining the results of multiple SELECT statements into a single result set. This chapter covered the basic syntax, using UNION and UNION ALL, using UNION with different columns, and sorting the combined result set. Understanding how to use the UNION operator effectively will enhance your ability to query and analyze your database data.