Using UNION Operator in Oracle

This article is written about how to use the Oracle UNION operator with syntax and examples.

Description

The Oracle UNION operator is used to mix the end result units of 2 or greater Oracle SELECT statements. It eliminates reproduction rows between the a number SELECT statements.

Each SELECT declaration inside the UNION operator ought to have the equal wide variety of fields in the end result sets with similar statistics types.

Syntax

The syntax for the UNION operator in Oracle/PLSQL 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 want to retrieve. tables The tables that you want to retrieve documents from. There ought to be at least one table listed in the FROM clause. WHERE conditions Optional. The prerequisites that must be met for the archives to be selected.

Note

There need to be identical range of expressions in each SELECT statements.

Example – Returns single field

The following is an example of the Oracle UNION operator that returns one subject from multiple SELECT statements (and each fields have the identical facts type):

SELECT supplier_id
FROM suppliers
UNION
SELECT supplier_id
FROM order_details;

In this Oracle UNION operator example, if a supplier_id appeared in each the suppliers and order_details table, it would appear once in your result set. The Oracle UNION operator gets rid of duplicates. If you do not wish to remove duplicates, try the usage of the Oracle UNION ALL operator.

Example – Using ORDER BY

The Oracle UNION operator can use the ORDER BY clause to order the consequences of the query.

For example:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id <= 500
UNION
SELECT company_id, company_name
FROM companies
WHERE company_name = 'Apple'
ORDER BY 2;

In this Oracle UNION operator, due to the fact that the column names are specific between the two SELECT statements, it is more fantastic to reference the columns in the ORDER BY clause with the aid of their function in the end result set. In this example, we have sorted the effects via supplier_name / company_name in ascending order, as denoted by means of the ORDER BY 2

The supplier_name / company_name fields are in role #2 in the result set.

Frequently Asked Questions

Question: I need to compare two dates and return the depend of a area based totally on the date values. For example, I have a date subject in a table referred to as remaining updated date. I have to test if trunc(last_updated_date >= trunc(sysdate – 13).

Answer: Since you are the usage of the Oracle COUNT feature which is an mixture function, we would suggest using the Oracle UNION operator. For example, you should try 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(a.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(a.last_updated_date) > TRUNC(sysdate - 13)
GROUP BY a.code, a.name;

The Oracle UNION operator approves you to function a matter based on one set of criteria.

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

As nicely as function a matter primarily based on some other set of criteria.

TRUNC(a.last_updated_date) > TRUNC(sysdate - 13)