Menu
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?
239
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
Actually you often need both "WHERE" and "JOIN". "JOIN" is used to retrieve data from two tables - based ON the values of a common column. If you then want to further filter this result, use the WHERE clause. For example, "LEFT JOIN" retrieves ALL rows from the left table, plus the matching rows from the right table.
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.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
