How to use UNION in SQL

# How to use UNION in SQL

clickHere
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.

## 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

### Syntax

` `
`SELECT column_namesFROM Table_1UNIONSELECT column_namesFROM Table_2Copy 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

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.

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, CTCFROM PermanentUNIONSELECT Name, CTCFROM FreelancerORDER BY CTC DESC;Copy code`

Output

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.

### 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, CTCFROM PermanentWHERE CTC > 15UNIONSELECT Name, CTCFROM FreelancerWHERE CTC > 15Copy code`

Output

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_namesFROM Table_1UNION ALLSELECT column_namesFROM 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: Types of Keys in Database

## UNION vs JOIN

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.