0

compare these two queries:....

Query 1

SELECT      *
FROM        TableA a
INNER JOIN  TableXRef x
        ON  a.ID = x.TableAID
INNER JOIN  TableB b
        ON  x.TableBID = b.ID
WHERE       a.ID = 1            /* <-- Filter here? */

Query 2

SELECT      *
FROM        TableA a
INNER JOIN  TableXRef x
        ON  a.ID = x.TableAID
        AND a.ID = 1            /* <-- Or filter here? */
INNER JOIN  TableB b
        ON  x.TableBID = b.ID
juergen d
  • 201,996
  • 37
  • 293
  • 362
Moin Shirazi
  • 4,372
  • 2
  • 26
  • 38
  • you can profile the time each query takes to execute http://stackoverflow.com/questions/11274892/measuring-actual-mysql-query-time – Luis Alves Jan 19 '15 at 13:03
  • WIth an `inner join`, the two queries should have the same execution plan, and hence the same performance. – Gordon Linoff Jan 19 '15 at 13:18

1 Answers1

0

Doesn't matter, the same thing should be executed in both cases.

Personally I prefer join conditions only in the ON clause and "filtering" in the WHERE clause.

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • It's because the database plans the execution of the query right? (using relational algebra) – Luis Alves Jan 19 '15 at 13:01
  • But i think so, the former query will not return any actual matches for a.id other than 1, so the latter syntax (with WHERE) is logically more consistent. – Moin Shirazi Jan 19 '15 at 13:04