This question is regarding the restrictions on the usage of the UNION operator. Shouldn't the names of the columns...
in the select queries match? Why does not Oracle put this restriction on column name specification? According to my opinion, there must be a restriction on the usage of the UNION operator saying that the names of the columns of the involved queries should match apart from the datatype and number of columns. This will reduce the probability of a programmer doing UNION on unrelated columns.
Can you please comment on this?
Thanks for the interesting question. My comment basically boils down to this: let us be glad that there is no such restriction on UNION. It would ruin a very useful SQL operator.
Suppose that the column names in the queries in a UNION did have to match, like this:
select foo, bar, qux, fap from HERE where this = 9 union select foo, bar, qux, fap from THERE where that = 37
How could this be useful? Obviously, if HERE is the same as THERE, we wouldn't even need UNION, we'd use an OR on THIS and THAT.
So HERE and THERE are different sources. But how different can they really be? After all, with the restriction in place, these sources have to share a whole bunch of columns! That certainly implies similar data. Note that separate sources with similar data is sometimes a warning sign of insufficient or improper modelling.
So with the restriction, the number of real world UNION queries we would be allowed to write will be limited.
We would never be able to write useful queries like this --
select regno as security , 'stock' as type , currvalue as asset_amt from STOCKS where custno = 42 union all select bond_id , 'bond' , npv from BONDS where ownerid = 187 order by asset_amt desc
As for preventing programmers from doing unrelated UNIONs, perhaps we should encourage them to do so. It takes extra programming to run the STOCKS and BONDS queries separately (as you would have to, if UNION restricted you to the same column names) and yet still show the results in the proper sequence. Lots of extra programming.
We should encourage programmers to look for SQL solutions, which are not only simpler to code, but often more efficient as well.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading