How are these statements processed?

1

Hello

Can you tell me please how the following are processed?

  1. SELECT * FROM TABLE1 LEFT INNER JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE1.COL2='Y'

  2. SELECT * FROM TABLE1 LEFT INNER JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE2.COL2='Y'

  3. SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE1.COL2='Y'

  4. SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COL1=TABLE2.COL1 AND TABLE2.COL2='Y'

I am interested how the AND statements are processed. It seems they do not behave like WHERE statements so I wonder what is their function.

Thanks!

2

Queries 1 to 3 are exactly the same as if you put Table2.Col2 = 'Y' etc in the WHERE clause.

Query 4 behaves differently with Table2.Col2 = 'Y' in the WHERE clause due to the logical order of query processing.
As it stands query 4 will show all the rows in Table1 with NULLs for the Table2 columns where Table2.Col2 <> 'Y'.
If you put Table2.Col2 = 'Y' into the WHERE clause NULL never equals 'Y' so the LEFT OUTER JOIN would be converted to an INNER JOIN. To get the same result with the WHERE clasue one would need to do (Table2.Col2 = 'Y' OR Table2.Col2 IS NULL)

Logical order of query processing also explains why SELECT clause expressions are not available in the WHERE clause etc.