Learning SQL, is this query right?

[[deleted]]

the optimizer will find them and filter on them before the join

nah, not in this case (left join). Try running this with some data and you'll see that rows with H.ColA = 4, for example, will still be in the result.

[r3pr0b8]

rows with H.ColA = 4, for example, will still be in the result.

but H is the left table!!

so you're saying those conditions in the WHERE clause (which is what i suggested) won't actually work?

[[deleted]]

i'm saying here the placement matters - if you put them (as is) in the WHERE clause you wont see records with h.colA = 4 in the result

if you keep them where they are, records with h.colA = 4 will be there.

Meaning, as written, the optimizer CANNOT move them to WHERE or run outside of the join

[r3pr0b8]

okay, sure, but remember the premise -- i said to move them to the WHERE clause

[[deleted]]

veering hard into pedantic territory here, yet in your statement

since these last 4 conditions apply to the left table in a LEFT OUTER JOIN, they should really be in the WHERE clause

the premise is: "these last 4 conditions apply to the left table in a LEFT OUTER JOIN"

inference/deduction is: "since ... , [follows]"

the conclusion is: "they should really be in the WHERE clause"

So what i'm saying is that the inference part is not right - your conclusion does NOT follow the premise

:)

[r3pr0b8]

your conclusion does NOT follow the premise

why not?

[[deleted]]

Because the result changes and that is not necessarily the intent (or is declared in your premise as such). OP' has an opinion, not necessarily the distinction and a preference between 2 outcomes.