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.