Monday, March 16, 2009

Doubts about UNION operation

I have some doubts about 'union' operation. I know that when two tables union together, duplicate rows will be removed in the result set. My doubts are that if there are duplicated rows in the same table, will Oracle remove those rows? I thus did a simple test. The answer is YES.

My test:

SQL> select * from t;

A
----------
1
2
3
4
5
2

6 rows selected.

Note: I have duplicated rows in table t;

SQL> select * from t1;

A
----------
5
3
8


Union all - Oracle preserves all the rows from table t and t1 as expected

SQL> select * from t1
2 union all
3 select * from t;

A
----------
5
3
8
1
2
3
4
5
2

9 rows selected.


Union - Note: Oracle removes duplicated rows from two tables as well as from the same table:

SQL> select * from t1
2 union
3 select * from t;

A
----------
1
2
3
4
5
8

6 rows selected.

No comments: