SQL UNION Operator: The Complete Guide

0
33
SQL UNION Operator Tutorial With Example | Union Clause in SQL

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.

SQL UNION Example Tutorial

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;

SQL UNION Operator Tutorial

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.

UNION ALL CLAUSE in SQL

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.

Union Clause in SQL

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;

What is the use of Union Operator

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.

Use of Union with where clause

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.

Another UNION EXAMPLE in SQL

 

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.

Leave A Reply

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.