Union vs. Union All

View All Public Scripts Login to Run Script

Script Name Union vs. Union All
Visibility Unlisted - anyone with the share link can access
Description This example is created to show that UNION and UNION ALL do not operate on the entire data set, but is related to where in the statement, a "UNION" is being placed
Area SQL General / SQL Query
Contributor aol@basecare.dk
Created Wednesday September 09, 2020

Statement 1

select 1 from dual union --all  
select 1 from dual union all  
select 1 from dual union all  
select 1 from dual union all  
select 1 from dual union all  
select 1 from dual


1
1
1
1
1
1

5 rows selected.

1
1
1
1
1
1

Statement 2

select 1 from dual union all  
select 1 from dual union all  
select 1 from dual union all  
select 1 from dual union --all  
select 1 from dual union all  
select 1 from dual


1
1
1

2 rows selected.

1
1
1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database