Problem solve Get help with specific problems with your technologies, process and projects.

Should column names in UNION queries match?

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?

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
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
    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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.