How to use UNION in SQL

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.

Table of Content

• What is UNION in SQL?
  • Syntax
  • Rules for applying UNION
• UNION ALL
• UNION vs JOIN

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

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

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

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

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

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

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.