A follow-up on the predicate discussion in the ON and WHERE Clause

This time, I am gonna recap what I've read lately on this topic and try to paraphrase it... First: there are some terms which need to be sorted out Preserving row table (aka PR) means any columns from this table will show up in the result set no matter if the join conditions are met NULL supplying table(aka NS) means any columns from this table will show up as NULL in the result set if the join conditions are not met ( if met, regular values will be pulled out from this table) Here is the example:

Table a

a1   a2
=======
1   10
2   20
9    30

Table b

a1   b1
=======
2    2
1   20

QUEREY

select *
from a
left join b
on a.a1 = b.a1  --Join predicate which you expect to see it anyway
and a.a1 =1    --That's the predicate I am talking about here


RESULT:

1 10 1 20
2 20 NULL NULL
9 30 NULL NULL

Explanation:

When a.a1 =1, b.a1 =1 , a.a1 =1, the join condition was met
all columns from PR a and NS b will be pulled out in the result set
(1 ,10,1,20)


when a.a1 = 2, the join was not met. All columns from a will STILL be pulled out
which is (2,20) because a is a PR table.However, columns from b will be not pulled
out since it's left outer join, only NULL will be provided because table b is a NS
table.so the result set is (2,20,NULL,NULL)

when a.a1 = 9, the join was not met. All columns from a will still be pulled out
which is (9,30).However, b is the NULL-supplying table which returns NULL. So the
result set is (9,30,NULL,NULL).

Therefore, the final result set is

(1 ,10,1,20)
(2,20,NULL,NULL)
(9,30,NULL,NULL)