EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL UNION
 

SQL UNION

Priya Pedamkar
Article byPriya Pedamkar

Updated May 26, 2023

SQL UNION

 

 

Introduction to SQL UNION

The UNION operator in standard query language (SQL) combines result sets obtained from two or more SELECT statements into a single result set. While combining the results of these queries, the 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. Still, unlike the UNION operator, it keeps all the duplicate records in the final result set.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

  • The number of columns fetched from multiple SELECT queries on which the UNION or UNION ALL operator must 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 the UNION operator and its comparison with UNION ALL.

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. The 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:

SQL UNION 1

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:

SQL UNION 2

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:

all the books available in the library

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 the UNION ALL instead of the UNION operator.

Code:

SELECT title
FROM borrowed_books
UNION ALL
SELECT title
FROM returned_books;

Output:

SQL UNION 4

What do we observe? We observe that UNION ALL operator fetches all records and does not discard the duplicate ones, unlike the 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:

long with their authors and current fee status in the library database

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:

Find the names of all the books

Conclusion

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

Recommended Articles

We hope that this EDUCBA information on “SQL UNION” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW