Basic about UNION Operator – using and example

This SQL tutorial explains how to use the SQL UNION operator with syntax and examples.

Description

The SQL UNION operator is used to combine the result sets of two or extra SELECT statements. It gets rid of reproduction rows between the a variety of SELECT statements.

Each SELECT statement within the UNION ought to have the identical number of fields in the result units with comparable statistics types.

What is the distinction between UNION and UNION ALL?

UNION eliminates replica rows. UNION ALL does no longer dispose of duplicate rows.

Syntax

The syntax for the UNION operator in SQL is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Parameters or Arguments

expression1, expression2, expression_n The columns or calculations that you desire to retrieve. tables The tables that you desire to retrieve documents from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. The prerequisites that have to be met for the archives to be selected.

Note

There have to be same number of expressions in both SELECT statements. The corresponding expressions should have the same data kind in the SELECT statements. For example: expression1 ought to be the equal information kind in both the first and 2d SELECT statement. See also the UNION ALL operator.

Example – Single Field With Same Name

Let’s appear at how to use the SQL UNION operator that returns one field. In this easy example, the subject in both SELECT statements will have the equal title and records type.

For example:

SELECT supplier_id
FROM suppliers
UNION
SELECT supplier_id
FROM orders
ORDER BY supplier_id;

In this SQL UNION operator example, if a supplier_id regarded in each the suppliers and orders table, it would show up as soon as in your result set. The UNION operator gets rid of duplicates. If you do no longer desire to dispose of duplicates, strive the usage of the UNION ALL operator.

Now, let’s explore this instance similarly will some data.

If you had the suppliers table populated with the following records:

supplier_id supplier_name 1000 Microsoft 2000 Oracle 3000 Apple 4000 Samsung

And the orders table populated with the following records:

order_id order_date supplier_id 1 2015-08-01 2000 2 2015-08-01 6000 3 2015-08-02 7000 4 2015-08-03 8000

And you executed the following UNION statement:

SELECT supplier_id
FROM suppliers
UNION
SELECT supplier_id
FROM orders
ORDER BY supplier_id;

You would get the following results:

supplier_id

1000

2000

3000

4000

6000

7000

8000

As you can see in this example, the UNION has taken all supplier_id values from both the suppliers table as properly as the orders table and back a blended end result set. Because the UNION operator removed duplicates between the end result sets, the supplier_id of 2000 only appears once, even even though it is discovered in both the suppliers and orders table. If you do no longer desire to remove duplicates, strive the use of the UNION ALL operator instead.

Example – Different Field Names

It is now not fundamental that the corresponding columns in every SELECT statement have the identical name, however they do want to be the equal corresponding facts types.

When you do not have the identical column names between the SELECT statements, it gets a bit tricky, particularly when you desire to order the effects of the question the usage of the ORDER BY clause.

Let’s appear at how to use the UNION operator with one of a kind column names and order the query results.

For example:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
UNION
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 1;

In this SQL UNION example, when you consider that the column names are different between the two SELECT statements, it is more effective to reference the columns in the ORDER BY clause through their role in the result set. In this example, we’ve sorted the outcomes by means of supplier_id / company_id in ascending order, as denoted by the ORDER BY 1. The supplier_id / company_id fields are in position #1 in the end result set.

Now, let’s discover this example similarly with data.

If you had the suppliers desk populated with the following records:

supplier_id supplier_name 1000 Microsoft 2000 Oracle 3000 Apple 4000 Samsung

And the companies table populated with the following records:

company_id company_name 1000 Microsoft 3000 Apple 7000 Sony 8000 IBM

And you executed the following UNION statement:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
UNION
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 1;

You would get the following results:

supplier_id supplier_name 3000 Apple 4000 Samsung 7000 Sony 8000 IBM

First, note that the report with supplier_id of 3000 solely appears as soon as in the result set because the UNION question removed reproduction entries.

Second, note that the column headings in the result set are called supplier_id and supplier_name. This is because these were the column names used in the first SELECT announcement in the UNION.

If you had wanted to, you ought to have aliased the columns as follows:

SELECT supplier_id AS ID_Value, supplier_name AS Name_Value
FROM suppliers
WHERE supplier_id > 2000
UNION
SELECT company_id AS ID_Value, company_name AS Name_Value
FROM companies
WHERE company_id > 1000
ORDER BY 1;

Now the column headings in the end result will be aliased as ID_Value for the first column and Name_Value for the 2nd column.

ID_Value Name_Value 3000 Apple 4000 Samsung 7000 Sony 8000 IBM

Frequently Asked Questions

Question: I need to examine two dates and return the depend of a area based on the date values. For example, I have a date discipline in a desk referred to as closing up to date date. I have to check if trunc(last_updated_date >= trunc(sysdate-13).

Answer: Since you are the usage of the COUNT characteristic which is an combination function, we’d propose the usage of the Oracle UNION operator. For example, you ought to strive the following:

SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
AND a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(last_updated_date) <= TRUNC(sysdate-13)
GROUP BY a.code, a.name
UNION
SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
AND a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(last_updated_date) > TRUNC(sysdate-13)
GROUP BY a.code, a.name;

The Oracle UNION permits you to operate a be counted primarily based on one set of criteria.

TRUNC(last_updated_date) <= TRUNC(sysdate-13)

As properly as function a rely based totally on some other set of criteria.

TRUNC(last_updated_date) > TRUNC(sysdate-13)