Re: SQLServer/Oracle Views

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 06/05/04

  • Next message: Hugo Kornelis: "Re: "Select Case" in TSQL" 
    Date: Sat, 05 Jun 2004 22:18:34 +0200

    On Fri, 4 Jun 2004 19:43:01 +0100, Mike John wrote:

    >This probably refers to views based on outer joins using the old (Non ansi) join syntax of *= being used in the where clause.These were of different syntax to how Oracle defined them. If you stick to the Ansi jojn syntax (left outer, right outer clauses etc) then I do not believe you will have any problems.
    >
    >Mike John

    (snip)

    Hi Mike,

    After reading Tibor's reply, I suddenly remembered something Joe Celko
    wrote many years ago (don't know if it's on Google - I think this was back
    in the haydays of CompuServe Forums).

    It's not a syntax difference of the old outer-join syntax, but a different
    interpretation of the same syntax. Consider the following two queries in
    new join syntax:

    (1a)
    SELECT *
    FROM TableA AS a
    LEFT OUTER JOIN TableB AS b
    ON a.Column1 = b.Column1
    AND b.Column2 IS NULL

    (2a)
    SELECT *
    FROM TableA AS a
    LEFT OUTER JOIN TableB AS b
    ON a.Column1 = b.Column1
    WHERE b.Column2 IS NULL

    If you convert these two different queries to "old-style" outer join
    syntax, both would read:

    (1b/2b)
    SELECT *
    FROM TableA AS a, TableB AS b
    WHERE a.Column1 *= b.Column1
    AND b.Column2 IS NULL

    If you execute this on MS SQL Server, you'll get the same results as query
    (1a). I have no access to any Oracle DB, but if I remember Joe's post
    correctly, this query will yield the same as (2a) on Oracle.

    Joe used this to illustratet why the old-style join syntax is ambiguis for
    outer joins. If one query can be interpreted in different ways and two
    mainstream products do in fact use different interpretations, you know
    it's time to change to the more standardized ANSI-style syntax for outer
    joins.

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: Hugo Kornelis: "Re: "Select Case" in TSQL"

    Relevant Pages

    • Re: CROSS JOIN
      ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
      (comp.databases)
    • Re: Difference in Left Join, Right Join
      ... RIGHT syntax exist. ... If you write SQL such that what you feel is the driving table is at ... The above is Oracle outer join syntax, ...
      (comp.databases.oracle.misc)
    • Re: CROSS JOIN
      ... WHERE syntax would never ... FROM Foo LEFT OUTER JOIN Bar ON a = 42 ... FROM Foo, Bar, Gulk ... You can see the "between-ness" relationship. ...
      (comp.databases)
    • Re: Not Common both Tables
      ... For exemple, I'm sure that the Full Outer Join is not available under ... I'm pretty sure that the Exitsstatement and the UNION ... For the syntax error, it's probably a missing AND in the first subquery of ... You can also use an Outer Join and select all the records which will have ...
      (microsoft.public.access.adp.sqlserver)
    • Quantcast