union vs union all in sql oracle
The UNION operator is a set operator that combines result sets of two or more SELECT statements into a single result set.
There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)
UNION statement
The following illustrates the syntax of the UNION operator that combines the result sets of two queries:
|
1 2 3 4 5 |
select name1 from person1 union select name2 from person2 union select name3 from person3 |
In this statement, the name1 , mame2 and name3 must have the same number of columns presented in the same order. In addition, the data type of the corresponding column must be in the same data type group such as number or character.
UNION example
|
1 2 3 4 5 |
select 'nametest' from dual union select 'nametest' from dual union select 'nametest' from dual |
Result union statment
nametest
UNION ALL statement
|
1 2 3 |
select name1 from person1 union all select name2 from person2union all select name2 from person2 |
UNION ALL example
|
1 2 3 4 5 |
select 'nametest' from dual union all select 'nametest' from dual union all select 'nametester' from dual |
Result union all statment
nametest
nametest
nametester
Reference :
https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all