The ON clause defines the relationship between the tables. The WHERE clause describes which rows you are interested in. Many times you can swap them and still get the same result, however this is not always the case with a left outer join.
Is there a difference between the WHERE and ON clause? Yes. ON should be used to define the join condition and WHERE should be used to filter the data.
Rows of the outer table that do not meet the condition specified in the On clause in the join are extended with null values for subordinate columns (columns of the subordinate table), whereas the Where clause filters the rows that actually were returned to the final output.
However, moving the WHERE condition to the ON clause applies it to the individual tables prior to joining. This enables the left join to retain rows from the left table even though some column entries of those rows (entries from the right tables) do not satisfy the WHERE condition.
The where
clause applies to the whole resultset; the on clause
only applies to the join in question.
In the example supplied, all of the additional conditions related to fields on the inner side of the join - so in this example, the two queries are effectively identical.
However, if you had included a condition on a value in the table in the outer side of the join, it would have made a significant difference.
You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause
For example:
select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 and t2.f4=1
select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 where t2.f4=1
- do different things - the former will left join to t2 records where f4 is 1, while the latter has effectively been turned back into an inner join to t2.
The first query is quicker than the second one as the join condition is more specific than the second one: it does not makes sense to return records that you will filter with the where clause (it would be better do not return them at all- query1)
Anyway it really depends by the query optimizer.
have a look at the below:
Is a JOIN faster than a WHERE?
It is important to understand the logical order of SQL operations when thinking about SQL syntax. JOIN
is an operator (and ON
belongs to the relevant JOIN
) in the FROM
clause. The FROM
clause is the first operation to be executed logically (optimisers can still choose to reorder things).
In your example, there isn't really a difference, but it is easy to construct one, as I've shown in this blog post about the difference between ON
and WHERE
in OUTER JOIN
(the example from the blog post uses the Sakila database):
First query
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE fa.film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;
Yields:
ACTOR_ID FIRST_NAME LAST_NAME COUNT
--------------------------------------
194 MERYL ALLEN 1
198 MARY KEITEL 1
30 SANDRA PECK 1
85 MINNIE ZELLWEGER 1
123 JULIANNE DENCH 1
Because we filtered the outer joined table in the WHERE
clause, the LEFT JOIN
was effectively turned into an INNER JOIN
. Why? Because if we had an actor that didn't play in a film, that actor's only row would have fa.film_id IS NULL
, and the fa.film_id < 10
predicate would thus yield NULL
. Such actors are excluded from the result, just as with an INNER JOIN
.
Second query
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
AND fa.film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;
Yields:
ACTOR_ID FIRST_NAME LAST_NAME COUNT
-----------------------------------------
3 ED CHASE 0
4 JENNIFER DAVIS 0
5 JOHNNY LOLLOBRIGIDA 0
6 BETTE NICHOLSON 0
...
1 PENELOPE GUINESS 1
200 THORA TEMPLE 1
2 NICK WAHLBERG 1
198 MARY KEITEL 1
Now, the actors without films are included in the result, because the fa.film_id < 10
predicate is part of the LEFT JOIN
's ON
predicate
Conclusion
Always place predicates where they make most sense logically.
JOIN
operation? Place them in ON
JOIN
product? Place them in WHERE
The two queries are NOT identical.
Mark Bannister was right in pointing out that the where
clause is applied to the whole result set but the on clause
applies to the join.
In your case, for SQL 1 LEFT JOIN conditions filter joins on the right but the left side is always returned before any WHERE filtering. Since there are no WHERE conditions all of t1 is always returned.
In SQL 2, the LEFT JOIN conditions filter some results showing up on the right but again all t1 is returned. But this time the WHERE conditions may filter some records of t1 away.
INSERT INTO `t1` (`f1`,`f2`,`f3`) VALUES (1,1,1);
INSERT INTO `t2` (`f3`) VALUES (1);
Since they point to different logic the query must be written based on that and it gives us great power and flexibility.
An INNER JOIN however returns the same result so yes check the optimiser.
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