SQL UNION Operator: The Complete Guide

SQL UNION operator combines a result set of two or more SELECT statements. 

SQL UNION

A UNION operator in SQL combines a result-set of two or more SELECT statements. The UNION operator removes the duplicate rows between the several SELECT statements.

1. Each SELECT statement within a UNION must have the same number of columns.
2. It should have the same number of column expressions and data types written in the same order.
3. The columns in each SELECT statement should also be in the same order.
4. The Union clause obtains the result set, excluding the duplicate rows between the various results sets.

In this article, we’ll review:

1. UNION and UNION all and how they work.
2. We discuss the rules for using UNION vs. UNION ALL.
3. SQL Operator Syntax.
4. How to use the simple SQL UNION clause in the select statement.
5. How to use the SQL UNION with the queries with a WHERE clause.

Syntax

See the following syntax.

Select columns from table_name where condition
UNION
Select columns from table_name where condition;

The UNION operator selects only the distinct values by default. However, we can use the UNION ALL operator to allow duplicate values.

See the following syntax.

Select columns from table_name where a condition 
UNION ALL
Select columns from table_name where condition;

Parameters

The columns are the name of the columns that you want to display.

Table_name is the name of the tables.

Where Conditions are optional. The conditions must be met for the records to be selected.

Example

See the following SQL Table. You can use ALTER TABLE query if you want to change the table.

Consider the table.

Employee

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

 

Department

Dept_no	Emp_id
123	301
214	401
125	505

 

See the following query.

Select Emp_id FROM Employee 
UNION 
Select Emp_id FROM Department;

Explanation

The UNION operator removed all the duplicates generated by both the select statements. You can see that Emp_Id 301 and 401 were displayed once.

If you do not wish to remove the duplicate values, try using the UNION ALL operator.

UNION ALL CLAUSE in SQL

The UNION ALL operator combines the results of the two SELECT statements, which include duplicate rows.

As previously discussed, the rules that apply to the UNION ALL clause are entirely the same as that of the UNION clause.

Now, see the following query.

Select Emp_id FROM Employee 
UNION  ALL 
Select Emp_id FROM Department;

See the output.

Explanation

As you can see that Emp_id 301 and 401 were displayed twice. This happened because we used UNION ALL operator and didn’t remove any duplicates.

Example: (Different Field Names)

Previously, you saw that the selected columns were of the same name. We will deal with queries with different column names, but they must have the same datatypes.

See the following query.

Select Emp_id from Employee 
UNION 
Select Dept_no from Department;

See the following output.

You can also use UNION ALL in the above query. See the below example.

Select Emp_id from Employee 
UNION ALL 
Select Dept_no from Department;

Use of Union with where clause

Consider the following tables.

CUSTOMER

ID	NAME	AGE	CITY
1	Rohit	20	Patna
2	Shivam	18	Jalandhar
3	Pranav	19	Dharamshala

 

ORDERS

O_ID	Cust_ID	City	AMOUNT
201	1	Patna	3000
202	2	Jalandhar	4000
203	4	Kolkata	1000

 

See the following query.

Select Id, city from customer 
where city = ”Patna” 
UNION 
select O_ID, city from orders where city = ”Patna”;

Also, see the below query.

Select Id, city from customer 
where city = ”Patna” 
UNION 
select cust_id, city from orders where city = ”Patna”;

See the output.

Another UNION EXAMPLE in SQL

We can also alias the column name using AS keyword for changing the column name for temporary results.

Let’s see the query.

Select Id AS Customer_ID, city  from customer
where city = ”Patna” 
UNION 
select cust_id AS Customer_ID, city from orders where city = ”Patna”;

See the following output.

 

There are two other clauses which are like the union clause

1. Intersect clause combines two select statements but returns only rows from the select statements identical to a row in the second SELECT statement.
2. Except clause is used to return rows from first select statements that are not returned by the second select statement.

If you want to compare the two dates and return a count of the field based on the date values. For example, I have a date field in the last updated date table. I have to check if the trunc(last_updated_date >= trunc(sysdate-13).

Since you are using a COUNT() aggregate function, we recommend using a UNION operator.

Summary

A few points must be considered when determining whether to use the UNION vs. UNION ALL. First, use UNION ALL if you know that the result sets from the multiple queries don’t overlap or generate duplicates and remember, if you need to use the parentheses, you can do that. You can also pivot and transform the output.

Make sure your data types match up, and if you need to do an ORDER BY, do it at the end after you’ve run all those set queries.

By nature, when it comes to UNION vs. UNION ALL, UNION ALL is faster than UNION, as UNION operators incur additional overhead to eliminate duplicates.

That’s it for this tutorial.