[thelist] followup SQL question

[Rudy_Limeback at maritimelife.ca]

>  SELECT * FROM Services, Operators
>   WHERE Services.OperatorID .= Operators.OperatorID
>
> did you see that dot before the = sign when you first 
> read it? many people don't, and if they do, they don't 
> know what it means. there goes my readability excuse. 

hi matt

is that some kind of outer join syntax?  which database?

sql/server uses an asterisk instead of a dot...

   SELECT * FROM Services, Operators
    WHERE Services.OperatorID *= Operators.OperatorID

oracle uses a plus sign in parentheses...

   SELECT * FROM Services, Operators
    WHERE Services.OperatorID = Operators.OperatorID (+)

the problem with both of these syntaxes is that you have to remember which 
side to put the asterisk or plus side on, because the "null effect" of the 
outer join (i.e. you get nulls in the columns coming from the table with 
unmatched rows) varies as to which table it applies to, if you know what i 
mean

(i mean, you would think that a "plus" sign might signify this is the side 
i always want a non-null, right? wrong)

i think mysql only allows the explicit outer join syntax...

   SELECT * FROM Services LEFT JOIN Operators
       ON  Services.OperatorID = Operators.OperatorID 

which i always found easier to understand anyway


rudy
r937.com