Difference between UNION and UNION ALL clause – Oracle

UNION and UNION ALL used to combine ( set operation ) two or more query results.  UNION will eliminate duplicate rows and UNION ALL will display all rows.

SQL> select * from table_a;

No
–
1
2
2

SQL> select * from table_b;

No
—–
2
3

SQL> select * from table_a UNION select * from table_b;

No
—
1
2
3

SQL> select * from table_a UNION ALL select * from table_b;

No
—
1
2
2
2
3

Things to remember writing UNION queries

1. Number of columns in each UNION query must match

g :- select col1,col2 from table_a UNION select col1 from table_b; — This will not work;

 Instead you can replace column with null clause to match the number of columns

2. Data types must match

 Eg :- select ‘a’ from table_a UNION select 1 from table_b; — This will not work;

3. For large data set queries UNION might have performance issues. So use it very carefully.