1

Some devs use JOIN and AND in this sql

SELECT * FROM A LEFT JOIN B
ON A.id = B.id AND B.date_begin < '2019' AND B.date_end > '2018'
WHERE B.group IN (..)

Which yield a different result if moving the condition in where part

SELECT * FROM A LEFT JOIN B
ON A.id = B.id 
WHERE B.group IN (..)
AND B.date_begin < '2019' AND B.date_end > '2018'

Why and how does the SQL is interpreted?

Thanh Trung
  • 3,566
  • 3
  • 31
  • 42
  • Could you post some sample data and expected behavior? – Turbo May 29 '19 at 08:53
  • When you say different result, does it mean it return different amount of rows or same amount of rows but different field values? – FanoFN May 29 '19 at 08:56
  • Your 2 SQLs use `Left join`, so the data would be based on A's data, and the `where` clause's function is to filter the data based on A, your filter condition is different ,so the result would get difference. – Shawn.X May 29 '19 at 09:03

1 Answers1

0

You can do both things but with the clause in the join you will have a more structured query. It doesn't matter if you have a small query but with around 50 or more you loose the focus. I can recommend to use the AND instead of plenty of where conditions.

Ignatius
  • 1,167
  • 2
  • 21
  • 30
heaxyh
  • 573
  • 6
  • 20