kramaswamy (11/19/2012)
Hm - I'm actually kinda surprised that works. I would have thought it would fail because the WHERE condition would evaluate AS@TableB_ID IS NULL (TRUE) OR TableB_ID (Value OR NULL) = @TableB_ID (NULL)
I would have thought that the second part of that would evaluate to undefined, since you are comparing a NULL value with an equals operator. Then, since that one is undefined, the whole condition would evaluate to undefined, since you're ORing TRUE with undefined.
I guess that the second condition though, will instead evaluate as FALSE instead of undefined, and thus allow it to work?
No, that is not exactly right. The second condition is not evaluated as FALSE and it's easy to check, let see the following example:
select *
from (values (1),(2),(3)) c(c)
where not (c = null)
if "c=null" evaluates as FALSE, then "not (c = null)" would evaluate to true and all records will be returned. But it doesn't happen as "c = null" evaluates to UNDEFINED, as you rightly thought in the first place!
The important bit in the used where condition is "OR", since OR evaluates to TRUE if any of the logical parts evaluates to TRUE, it doesn't really care that "TableB_ID = @TableB_ID(NULL)" evaluates as UNDEFINED!
So, used WHERE works simply like that:
If @TableB_ID is not null, it will check if the value in TableB_ID from LinkedTable is equal to it, basically turning LEFT JOIN to INNER JOIN!
If @TableB_ID is null, JOIN is not working at all as condition evaluates to UNDEFINED, but this is ignored completely as part of evaluating result of logical OR operator, which returns true for @TableB_ID IS NULL