EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL UNION

SQL UNION

By Priya PedamkarPriya Pedamkar

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.

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more