Should column names in UNION queries match?

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle and SQL Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to use the Oracle Database SQL Reference Manual How to use SQL Developer to run SQL statements How to work with the Oracle database home page How to use SQL*Plus in Oracle How to use SQL Developer to work with an Oracle database How to view and edit table column definitions How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary autonomous transaction  (SearchOracle.com) CFML  (SearchOracle.com) dynamic SQL  (SearchOracle.com) foreign key  (SearchOracle.com) Java Database Connectivity  (SearchOracle.com) Open Database Connectivity  (SearchOracle.com) Oracle  (SearchOracle.com) stored procedure  (SearchOracle.com) The Open Group  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.