Outer Join to a Value?

[Moebius01]

I just had a co-worker ask me a question about a query, and something in the context caught me off guard. He was asking about a (+) after a field name which I explained. But then when I was looking at the rest of the query, I noticed a join to a value. Basically, the where clause looked kind of like this.

WHERE
tablea.field1(+) = tableb.field1
AND
tablea.field2(+) = "some value"

Is there some reason I'm missing that this you would include the (+) on the second field? It would seem that's the same as tablea.field2 = "some value".

 

[sem]

No. Omitting (+) will convert your outer join to inner. When there's no corresponding row in tablea, all its fields in result set (including field2) are replaced with NULL. Though NULL doesn't equal to "some value", thus this row will be rejected.

Regards, Dima

http://seminihin.narod.ru