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

Advertisements

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.

10 thoughts on “Difference between UNION and UNION ALL clause – Oracle”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s