Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

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

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 17 Mar 2003 15:54:33 -0800
Message-ID: <c0d87ec0.0303171554.34919a58@posting.google.com>


>> 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 Mon Mar 17 2003 - 17:54:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US