0

Shuold I use INNER JOIN conditions as a WHERE conditions? Consider these two sample queries to explain the question:

SELECT t1.*, t2.* 
FROM table1 AS t1 
INNER JOIN table2 AS t2 
    ON t1.id = t2.foreign_key 
WHERE t1.year < 2014

and this without the WHERE clause

SELECT t1.*, t2.* 
FROM table1 AS t1 
INNER JOIN table2 AS t2 
    ON  t1.id = t2.foreign_key 
        AND t1.year < 2014

Since the JOIN type is INNER, both queries will result on typical result set.

Which is better in term of performance?

Sergio
  • 6,900
  • 5
  • 31
  • 55
doc_id
  • 1,363
  • 13
  • 41

2 Answers2

0

Try to check your query plans, should be near identical. Also, db engine can optimize query to a better execution plan, so there should be no difference

Sergio
  • 6,900
  • 5
  • 31
  • 55
0

Generally performance should be similar since both queries should execute in the same way (if query optimizer is good).

I usually use WHERE clause since having simple join condition make sure that index scan will be used (if there is appropriate index).

For eaxample if you have slightly change in your query (see conditions order):

SELECT t1.*, t2.* 
FROM table1 AS t1 
INNER JOIN table2 AS t2 
    ON t1.year < 2014
        AND t1.id = t2.foreign_key

Some optimizer engines could decide not to use index on t2.foreign_key column.

semao
  • 1,757
  • 12
  • 12
  • Interesting. Assuming there is an index on t2.foreign_key, and no index on t1.year, it would be effecient for the engine to flip the order of those two conditions, utilizing the indexes to limit the result, and only do resultset scan for the other (year) condition, not table scan. – doc_id Feb 06 '14 at 12:23