What is wrong with this query?
(ran against oracle 10g)
[sql]
SQL> select count(*) as count from table1 UNION select count(*) as count from table2;
COUNT
----------
1
[/sql]
The part that is wrong is that each query individually returns one row; so you would expect a union of those two queries to be both of the queries appended to each other; that would be two rows right? wrong. but why?
It gets more interesting though; if you get different values, you get multiple rows back. Here table2 has one record, and table1 has none
[sql]
SQL> select count(*) as count from table1 UNION select count(*) as count from table2;
COUNT
----------
0
1
[/sql]
ok so that worked fine; if we add a record into table1 so they each have 1 record we see as above that there is only one result returned.
[sql]
SQL> select count(*) as count from table1 UNION select count(*) as count from table2;
COUNT
----------
1
[/sql]
(the same thing happens in every case where both tables have the same count)
here oracle drops the ball and only gives one row back when clearly it should return two.
well sort of; as it turns out there are two phrases to use: UNION and UNION ALL. Guess which one will return both rows and won’t weed out duplicates
But would it not have been better if they chose to allow union to work just like a select statement and by default return all the rows; and only by adding UNION DISTINCT have it drop duplicates?
[sql]
SQL> select count(*) as count from table1 UNION ALL select count(*) as count from table2;
COUNT
----------
1
1
[/sql]