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.

Also read :

Difference between DECODE and CASE
Oracle Cursors
Find Nth highest salary
Polymorphism in Oracle
Install Notepad++ in Ubuntu
Oracle 12c New Features
Oracle Interview questions and answers
5 Different ways to delete records Oracle
Nth highest salary

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated. View all posts by sqlandplsql