And, Where usage tips And left join prompts And precautions with +

Please pay attention to the abuse of 1and and where.

The effect of the three paragraphs below must be different.
(1)
Copy codeThe Code is as follows:
SelectA. a1, B. b3
FromAleftjoinB
OnA. a2 = B. b2
WhereA. a3 = 1
AndB. b3 = 2

First join left and then filter B in the join result. b3 = null, then you add B after where. b3 = 2 then B in the result. b3 is certainly not null.
(2)
Copy codeThe Code is as follows:
SelectA. a1, B. b3
FromAleftjoinB
OnA. a2 = B. b2
AndB. b3 = 2
WhereA. a3 = 1

Leave the b3 = 2 condition of B before Association, and then associate it on the left. This result may be null in B. a3.
(3)
Copy codeThe Code is as follows:
SelectA. a1, B. b3
FromAleftjoinB
OnA. a2 = B. b2
AndB. b3 = 2
AndA. a3 = 1

Before Association, the B3 = 2 in B and a3 = 1 in A are dropped before Association. Then, this result may be null in B. b3.

So when you use where and, pay more attention to the location.

2. Use + for left-join defects. A solution may be available for future use.
(1)
Copy codeThe Code is as follows:
SelectA. a1, B. b3
FromA, B
WhereA. a2 = B. b2 (+)
AndA. a3 (+) = 1
AndB. b3 (+) = 2

This condition should be the same as that of (1) in 1.

First join left and then filter B in the join result. b3 = null, then you add B after where. b3 = 2 then B in the result. b3 is certainly not null.
That is to say, using + for left join is not used. leftjoin is flexible. You can see if there is any good solution later.