SQL UNION

Introduction to SQL UNION

UNION operator in standard query language (SQL) is used to combine result sets obtained from two or more SELECT statements into a single result set. While combining the results of these queries, UNION operator removes all the duplicate values from the final result set. Another popular operator similar to this operator is UNION ALL which is also used to combine the results obtained from two or more SELECT queries but unlike UNION operator, it keeps all the duplicate records in the final result set.

While using UNION operator, we should adhere to following points:

• The number of columns being fetched from multiple SELECT queries on which UNION or UNION ALL operator has to be applied must be the same.

Example:

Code:

SELECT name FROM table1
UNION
SELECT name FROM table2;

• The columns that have to be selected should have the same data type.
• The sequence of the columns appearing in the SELECT statements must be the same.

Let us have a look at the pictorial depiction of UNION operator and its comparison with UNION ALL.

Syntax and parameters of SQL UNION

The basic syntax for writing SELECT queries with UNION operators is as follows:

SELECT column_name
FROM table_name_1
UNION
SELECT column_name
FROM table_name_2
UNION
SELECT column_name
FROM table_name_3
.
.
.
UNION
SELECT column_name
FROM table_name_n

The parameters used in this syntax are as follows:

• column_name: column_name corresponds to the name of the column on which we want to use the UNION operator and want to fetch it for the final result set.
• FROM table_name_1: table_name_1 is the name of the first table from which the records have to be fetched.
• FROM table_name_2: table_name_2 is the name of the second table from which the records have to be fetched. Same is the case with table_name_3, …, table_name_n.

Of the above mentioned arguments, all of them are mandatory. However, you may use WHERE, , ORDER BY, GROUP BY and HAVING clauses to this syntax based on your requirements.

Examples of SQL UNION

Given below are the examples of SQL UNION:

Let us create two dummy tables called “borrowed_books” and “returned_books”. These tables contain details of books borrowed and returned to a library on a daily basis. We can use the following CREATE statements to create the said tables.

Creating borrowed_books table.

Code:

CREATE TABLE borrowed_books (
book_id INT NOT NULL PRIMARY KEY,
title VARCHAR(100)  NOT NULL,
author_name  VARCHAR(100),
genre VARCHAR(100),
updated_at DATE,
fee_status VARCHAR(100)
);

Creating returned_books table.

Code:

CREATE TABLE returned_books (
book_id INT NOT NULL PRIMARY KEY,
title VARCHAR(100)  NOT NULL,
author_name  VARCHAR(100),
genre VARCHAR(100),
updated_at DATE,
fee_status VARCHAR(100)
);

Let us insert a few records in them to work with using the following INSERT statements.

Code:

INSERT INTO public.borrowed_books(
book_id, title, author_name, genre, updated_at, fee_status)
VALUES
(1, 'The Choice','Edith Eva Eger','Memoir','2020-06-06','Paid'),
(2,'Deep Work','Carl Newport','Self Help','2020-06-16', 'Pending'),
(3, 'A Man Called Ove','Fredrik Backman','Fiction','2020-06-18','Pending'),
(4,'When Breath Becomes Air','Paul Kalanithi','Memoir','2020-06-01','Paid'),
(5,'Man Search for Meaning','Viktor Frankl','Memoir','2020-06-18', 'Pending'),
(6,'The Third Pillar','Raghuram Rajan','Economics','2020-06-11', 'Paid');
select * from borrowed_books;

Output:

Code:

INSERT INTO public.returned_books(
book_id, title, author_name, genre, updated_at, fee_status)
VALUES
(1,'The Choice','Edith Eva Eger','Memoir','2020-06-16','Paid'),
(2,'The Third Pillar','Raghuram Rajan','Economics','2020-06-11', 'Paid'),
(3,'Data Structure & Algorithms','Thomas H. Cormen','Academic','2020-06-18','Paid'),
(4,'When Breath Becomes Air','Paul Kalanithi','Memoir','2020-06-21','Paid'),
(5,'Godaan','Premchand','Fiction','2020-06-15', 'Pending'),
(6,'Deep Work','Carl Newport','Self Help','2020-06-16', 'Pending');
select * from returned_books;

Output:

Example #1

Find the names of all the books available in the library.

Code:

SELECT title
FROM borrowed_books
UNION
SELECT title
FROM returned_books;

Output:

In this example, we can observe that all the books have been fetched only once. The duplicate records have been discarded. Let us compare this query with the next query that uses UNION ALL instead of UNION operator.

Code:

SELECT title
FROM borrowed_books
UNION ALL
SELECT title
FROM returned_books;

Output:

What do we observe? We observe that UNION ALL operator fetches all records and does not discard the duplicate ones unlike UNION operator.

Example #2

Find the names of all the books along with their authors and current fee status in the library database.

Code:

SELECT title, author_name, fee_status
FROM borrowed_books
UNION
SELECT title, author_name, fee_status
FROM returned_books;

Output:

Example #3

Find the names of all the books along with their id, authors and current fee status in the library database, for which the library fee is still pending.

Code:

SELECT book_id, title, author_name, fee_status
FROM borrowed_books
WHERE fee_status = 'Pending'
UNION
SELECT book_id, title, author_name, fee_status
FROM returned_books
WHERE fee_status = 'Pending';

Output:

Conclusion

UNION operator in SQL is used to combine results from two or more tables. When combining results from two tables, UNION operator keeps only unique values in the final result set.

This is a guide to SQL UNION. Here we discuss the introduction to SQL UNION along with appropriate syntax, parameters and query examples. You may also have a look at the following articles to learn more –

1. Natural Join in MySQL
2. Alternate Key in SQL
3. EXPLAIN in SQL
4. SQL Minus