Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

>> You might ask Joe whether deprecating column ordinals now allows:
 

 SELECT P#, PNAME
  FROM P
  WHERE P# < 50
 UNION
 SELECT PNAME, P#
  FROM P
  WHERE P# > 50;    Or must the ordinal of P# still match? <<

(Remember that "#" is not legal syntax and you need a semi-colon. Picky, picky). The result of a UNION [ALL] has no column names. It is a set in its own right, I have to now name the columns explicitly if I want to reference them. In the older SQLs, I could add an "ORDER BY 1,2" clause or even a "GROUP BY 1,2" clause using ordinal positions since there were no column names.

SELECT r, s
  FROM (SELECT a, b FROM Foo WHERE a < 50

        UNION 
        SELECT x, y FROM Fum WHERE x > 50
        UNION 
        SELECT g, (h+5) FROM Fee WHERE (h+5)= 50)
        AS FooFumFee (r, s); 

Here is an example where the column names are all over the place. The choice is either positional matching or a massive re-naming of everything. We opted to put column names in one place -- the final resultset -- and use positional matching (the phrase is "union compatible" in the Standard).

The positional matching in the SELECT is because all of the ISO standard procedural host languages use a sequential file model for the cursors that convert a set into a sequence (the one exception I know is PL/I and the GET DATA statement; any other?). Since SQL cannot operate outside a host language, we made concessions to avoid really convoluted syntax. Received on Tue Mar 18 2003 - 00:54:33 CET