[thelist] SQL Joins - ANSI 92 syntax

[rudy]

> this syntax is not ANSI 92 compliant and there is a good reason
> that the standard doesn't support this syntax (potential ambiguities
> in order of joins when you mix inner and outer joins).  Granted, this
> query is simple enough (relatively) that there is no potential ambiguity
> for the parser, but I prefer to cultivate good habits.  Am I correct
> however in this understanding?

hi steve

yes

outer joins using the table list syntax have always been problematic

in oracle, for example, you could say

    where table1.two_id (+) = table2.two_id

which isn't compatible with all other databases

see http://urlizer.com/00/636/ for more on this

> ANSI 92 syntax would make the join explicit in the FROM clause,
> rather than in the WHERE clause:
>
>  SELECT table1.one_name
>    FROM table1 INNER JOIN table2
>        ON table1.two_id = table2.two_id
>   WHERE table2.two_name != 'George'

actually, the standard sql inequality operator is <>

you can also say

    SELECT table1.one_name
       FROM table1 INNER JOIN table2
           ON table1.two_id = table2.two_id
                AND table2.two_name <> 'George'

which might run faster (your mileage may vary) because rows from table2 can
be eliminated by the join operation (which could be using indexes) before
candidate rows are returned to the WHERE logic

rudy