SQL Inner Join. ON condition vs WHERE clause

Question

I am busy converting a query using the old style syntax to the new join syntax. The essence of my query is as follows :

Original Query

SELECT i.*  
FROM 
  InterestRunDailySum i, 
  InterestRunDetail ird, 
  InterestPayments p
WHERE 
   p.IntrPayCode = 187
   AND i.IntRunCode = p.IntRunCode AND i.ClientCode = p.ClientCode
   AND ird.IntRunCode = p.IntRunCode AND ird.ClientCode = p.ClientCode

New Query

SELECT  i.*
  FROM InterestPayments p
    INNER JOIN InterestRunDailySum i 
      ON (i.IntRunCode = p.IntRunCode AND i.ClientCode = p.ClientCode)
    INNER JOIN InterestRunDetail ird 
      ON (ird.IntRunCode = p.IntRunCode AND ird.IntRunCode = p.IntRunCode)
  WHERE 
    p.IntrPayCode = 187

In this example, "Original Query" returns 46 rows, where "New Query" returns over 800

Can someone explain the difference to me? I would have assumed that these queries are identical.

score 19 · Accepted Answer · answered Jun 22 '12 at 14:24

19

The problem is with your join to InterestRunDetail. You are joining on IntRunCode twice.

The correct query should be:

SELECT  i.*
  FROM InterestPayments p
    INNER JOIN InterestRunDailySum i 
      ON (i.IntRunCode = p.IntRunCode AND i.ClientCode = p.ClientCode)
    INNER JOIN InterestRunDetail ird 
      ON (ird.IntRunCode = p.IntRunCode AND ird.ClientCode = p.ClientCode)
  WHERE 
    p.IntrPayCode = 187

answered Jun 22 '12 at 14:24

Kevin Aenmey

13,259
5
46
45

2

Thank you Kevin, I feel like a fool for not spotting that!! – Russell Jun 22 '12 at 14:42
3

It happens to all of us. :-) – Kevin Aenmey Jun 22 '12 at 14:44

score 6 · Answer 2 · edited Jul 29 '20 at 18:25

The "new query" is the one compatible with the current ANSI SQL standard for JOINs.

Also, I find query #2 much cleaner:

you are almost forced to think about and specify the join condition(s) between two tables - you will not accidentally have cartesian products in your query. If you happen to list ten tables, but only six join conditions in your WHERE clause - you'll get a lot more data back than expected!
your WHERE clause isn't cluttered with join conditions and thus it's cleaner, less messy, easier to read and understand
the type of your JOIN (whether INNER JOIN, LEFT OUTER JOIN, CROSS JOIN) is typically a lot easier to see - since you spell it out. With the "old-style" syntax, the difference between those join types is rather hard to see, buried somewhere in your lots of WHERE criteria.....

Functionally, the two are identical - #1 might be deprecated sooner or later by some query engines.

Also see Aaron Bertrand's excellent Bad Habits to Kick - using old-style JOIN syntax blog post for more info - and while you're at it - read all "bad habits to kick" posts - all very much worth it!

Good answer - but his queries aren't actually identical - it looks like it was just a typo ;-) — N West, Jun 22 '12 at 14:29

SQL Inner Join. ON condition vs WHERE clause

2 Answers2

Linked