1

I ask myself what is the more performance method when you join table and use JOIN in clause FROM and when you need to use BETWEEN :

  • Use Between in the FROM

or

  • Use between in the WHERE

Exemple :

SELECT *
FROM Tab1 a inner join Tab2 b 
on (a.pk=b.fk_1 AND a.date between b.date1 and b.date2)

Versus

SELECT *
FROM Tab1 a inner join Tab2 b 
on a.pk=b.fk_1  
WHERE a.date between b.date1 and b.date2

Which one is the best?

Thank

P.S : sorry for my bad english

Maxime Pavy
  • 11
  • 6
  • Take a llok here http://stackoverflow.com/questions/1018952/condition-within-join-or-where – Kostis Feb 16 '17 at 15:49
  • Try them both and see which one has the best performance. If the difference is negligible, then it doesn't matter anyway. – ivospijker Feb 16 '17 at 15:49
  • 1
    They should both produce the same execution plan and optimization. I prefer the first version, because the `between` condition combines columns from both tables, so it is logically part of the `join` condition. – Gordon Linoff Feb 16 '17 at 15:53
  • Thank Kostis, this answer my question – Maxime Pavy Feb 16 '17 at 15:56
  • performs wise they should be the same, but preference, I prefer the second in that `ON` should be for relationships and `WHERE` used for other filtering – Stephen Feb 16 '17 at 16:04

0 Answers0