Using UNION ALL Operator in Oracle

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

Description

The Oracle UNION ALL operator is used to mix the result sets of 2 or more SELECT statements. It returns all rows from the question and it does not do away with duplicate rows between the more than a few SELECT statements.

Each SELECT announcement inside the Oracle UNION ALL operator need to have the same variety of fields in the result units with similar statistics types.

Syntax

The syntax for the UNION ALL operator in Oracle/PLSQL is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION ALL
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 wish to retrieve information from. There have to be at least one desk listed in the FROM clause. WHERE conditions Optional. The conditions that have to be met for the data to be selected.

Note

There have to be same wide variety of expressions in both SELECT statements.

Example – Return single field

The following is an instance of the Oracle UNION ALL operator that returns one field from a couple of SELECT statements (and each fields have the equal records type):

SELECT supplier_id
FROM suppliers
UNION ALL
SELECT supplier_id
FROM orders;

This Oracle UNION ALL operator would return a supplier_id multiple times in your end result set if the supplier_id regarded in both the suppliers and orders table. The Oracle UNION ALL operator does no longer get rid of duplicates. If you desire to get rid of duplicates, try the use of the Oracle UNION operator.

Example – Using ORDER BY

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

For example:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'California'
UNION ALL
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

In this Oracle UNION ALL operator, when you consider that the column names are special between the two SELECT statements, it is more positive to reference the columns in the ORDER BY clause by their role in the result set. In this example, we’ve got sorted the effects through supplier_name / company_name in ascending order, as denoted by the ORDER BY 2

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