How to use UNION in SQL

How to use UNION in SQL

4 mins read471 Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Nov 22, 2022 19:38 IST

UNION in SQL is similar to the union of two sets in mathematics that is equal to the set of element that are present in both the set. Similarly UNION in SQL combines the results of two SELECT queries. In this article we will discuss UNION, UNION ALL and difference between JOIN and UNION in SQL.

2022_07_SQL-Union.jpg

Table of Content

What is UNION in SQL?

Union in SQL combines the data from the result of two SELECT statements into a single distinct result set. The resultant set doesn’t contain any duplicate value. 

Must Read: SQL Comparison and Arithmetic Operator

Must Read: SQL Logical Operator

Syntax

 
SELECT column_names
FROM Table_1
UNION
SELECT column_names
FROM Table_2
Copy code

Rules for applying UNION

  1. Both the SELECT statement must have the same number of columns.
  2. Columns in the SELECT statement must be in the same order.
  3. The Selected columns must have the same data type in the same order as the columns of the first table.

Now let’s take an example to get a better understanding of How to use UNION in SQL

Must Read: SQL LIMITS

Must Read: Introduction to SQL

Example -1:

In the above example, we have taken two tables Employee table and the Manager Table which contains the ID, name, and age. When we apply the UNION operator over the age field we get only 6 distinct entries from both the tables ( all the duplicate entries are removed).

Now, we will discuss some more examples of UNION in SQL:

We have two Employee tables (Permanent and Freelancers) of a company that contains the Employee ID, Name, Gender, and CTS of the employees.

Employee ID Name Gender CTC(in Lacs)
1001 Ajay M 15
1002 Babloo M 23
1003 Fredy F 15
1004 Dheeraj M 12
1005 Evina F 16
Permanent
Employee ID Name Gender CTC(in Lacs)
1006 Garima M 10
1007 Chhavi F 15
1008 Hans M 8
1009 Ivanka F 7
1010 Jai M 16
Freelancer

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

UNION with ORDER BY

Example – 2: Find the name and Salary of each permanent and freelancers employee and arrange the salary in descending order.

Query

 
SELECT Name, CTC
FROM Permanent
UNION
SELECT Name, CTC
FROM Freelancer
ORDER BY CTC DESC;
Copy code

Output

Name CTC (in Lacs)
Babloo 23
Evina 16
Jai 16
Ajay 15
Fredy 15
Chhavi 15
Dheeraj 12
Garima 10
Hans 8
Ivanka 7

the above table is arranged according to the CTC, but if you notice when the CTC in both the tables are same ( Evina and Jai -16 lacs, Ajay, Fredy, and Chhavi – 15 lacs) the entries of the second table are arranged after all the entries of the first table.

Must Read: SQL ORDER BY

Must Read: SQL WHERE

UNION with WHERE clause

Example – 3 Find the name and salary of each employee (permanent and freelance) whose salary is greater than 15 lacs.

Query

 
SELECT Name, CTC
FROM Permanent
WHERE CTC > 15
UNION
SELECT Name, CTC
FROM Freelancer
WHERE CTC > 15
Copy code

Output

Name CTC (in Lacs)
Babloo 23
Evina 16
Jai 16

Must Read: Subqueries in SQL

Must Read: Aggregate and Scalar Function in SQL

UNION ALL

As we mentioned above UNION of two tables doesn’t contain duplicate values but if we want all the entries of the selected field then we use UNION ALL.

In simple terms, UNION ALL is an extension of UNION, which returns the duplicate values from the result set of two queries.

Syntax

 
SELECT column_names
FROM Table_1
UNION ALL
SELECT column_names
FROM Table_2;
Copy code

Now, we will take the same example – 1 and apply UNION ALL in place of UNION and will check what difference comes in the result:

Example – 4:

Here in the above example we have taken the same two tables as in example 1 but in example 1 the result contains six distinct entries but in this example, the result contains ten entries in which some entries are duplicate too.

Must Read: How to Create, Insert, and Delete SQL views?

Must Read: Types of Keys in Database

Must Read: Introduction to Normalization

UNION vs JOIN

JOIN UNION
Combines multiple tables based on the matching fields (columns) Combines the result of multiple SELECT statements
Number of the selected field may or may not be the same Number of the selected field must be the same
Data type of column may or may not be the same Data type of column must be same
Combine the data in column and may or may not return distinct columns Combine the data in row and must return distinct rows

Must Check: SQL Online Course and Certifications

Conclusion

In this article, we have discussed how to use UNION in SQL with the help of examples. We have also discussed about UNION ALL and what is the difference between JOIN and UNION in SQL.

Hope this article, will help you in your data science/data analysis journey.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments