SQL JOIN where to place the WHERE condition?

sql join where
Aley picture Aley · Mar 18, 2013 · Viewed 88.6k times · Source

I have two following examples.

1. Example (WHERE)

SELECT 1
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id
 WHERE t2.field = true

2. Example (JOIN AND)

SELECT 1
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id AND t2.field = true

What is the faster way in terms of performance? What do you prefer?

Answer

Sebas picture Sebas · Mar 18, 2013

If a filter enters in a JOIN condition functionally (i.e. it is an actual join condition, not just a filter), it must appear in the ON clause of that join.

Worth noting:

  • If you place it in the WHERE clause instead, the performances are the same if the join is INNER, otherwise it differs. As mentioned in the comments it does not really matter since anyway the outcome is different.

  • Placing the filter in the WHERE clause when it really is an OUTER JOIN condition implicitely cancels the OUTER nature of the condition ("join even when there are no records") as these filters imply there must be existing records in the first place. Example:

... table1 t LEFT JOIN table2 u ON ... AND t2.column = 5 is correct

... table1 t LEFT JOIN table2 u ON ... 
WHERE t2.column = 5

is incorrect, as t2.column = 5 tells the engine that records from t2 are expected, which goes against the outer join. Exception to this would be an IS NULL filter, such as WHERE t2.column IS (NOT) NULL (which is in fact a convenient way to build conditional outer joins)

  • LEFT and RIGHT joins are implicitely OUTER joins.

Hope it helped.

Related questions

SQL query through an intermediate table

Given the following tables: Recipes | id | name | 1 | 'chocolate cream pie' | 2 | 'banana cream pie' | 3 | 'chocolate banana surprise' Ingredients | id | name | 1 | 'banana' | 2 | 'cream' | 3 | 'chocolate' RecipeIngredients | recipe_id | ingredient_id | 1 | 2 | 1 | 3 | 2 | 1 | 2 | 2 | 3 | 1 | 3 | 3 How do I construct a SQL query to find recipes where ingredients.name = …

Read More
SQL - JOIN BETWEEN vs WHERE BETWEEN

A few weeks ago I did a query (can't remember what for) and it involved the BETWEEN operator. At first, I used this in the WHERE clause, but it didn't produce the correct result set (again, I don't remember what …

Read More
Which performs first WHERE clause or JOIN clause

Which clause performs first in a SELECT statement? I have a doubt in select query on this basis. consider the below example SELECT * FROM #temp A INNER JOIN #temp B ON A.id = B.id INNER JOIN #temp C ON …

Read More