Oracle: Union All

UNION ALL Operator in SQL is similar to the UNION Operator. It helps to return all the records present in the Sets between which this operator applies. The only difference Between UNION and UNION ALL Operator occurs only when it comes to duplicate values.

UNION wherein does not support returning of the duplicate value multiple times. UNION ALL returns values even though they repeat.

For example:

a = {1,2,3,4,5,6,7}

b = {2,3,7,8,9}

a UNION b = {1,2,3,4,5,6,7,8,9} i.e. the multiple values are not repeated here.

However, a UNION ALL b = {1,2,2,3,3,4,5,6,7,7,8,9} i.e. the value returned even though they are repeated.

Syntax

Select Name from Stu
UNION ALL
Select Name from Children;

Example - SQL

We have the following Stu and Children table as shown below:

Now, we want to check all the total entries of name from both the table STu and Children:

Select Name from Stu
UNION ALL
Select Name from Children;

Output

Here, you can observe that duplicate names are present in the list.