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

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s