A question that is frequently occurring among my
SQL training‘s participants is:
What’s the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause?
I can definitely see how that’s confusing some people, as there
seems to be no difference at first sight, when running queries like these, e.g. in Oracle. I’m using the
Sakila database, as always:
-- First query
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
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) DESC;
This will yield something like:
ACTOR_ID FIRST_NAME LAST_NAME COUNT
--------------------------------------
108 WARREN NOLTE 3
162 OPRAH KILMER 3
19 BOB FAWCETT 2
10 CHRISTIAN GABLE 2
53 MENA TEMPLE 2
137 MORGAN WILLIAMS 1
2 NICK WAHLBERG 1
Of course, we could have written this instead, and received the same result:
-- Second query
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
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) DESC;
Now, I’ve moved the
FILM_ID < 10 filter from the
WHERE clause to the
ON clause. But the execution plan is the same for both queries:
---------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 |
| 1 | SORT ORDER BY | | 49 |
| 2 | HASH GROUP BY | | 49 |
|* 3 | HASH JOIN | | 49 |
|* 4 | INDEX FAST FULL SCAN| PK_FILM_ACTOR | 49 |
| 5 | VIEW | VW_GBF_7 | 200 |
| 6 | TABLE ACCESS FULL | ACTOR | 200 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="FA"."ACTOR_ID")
4 - filter("FA"."FILM_ID"<10)
It does not seem matter at all. Both queries yield the same result as well as the same plan. So…
Are ON and WHERE really the same thing?
They
are when you run an inner join. But they are not when you run an outer join.
And now, let’s compare these two queries here:
-- 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;
Yielding
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
Notice that with this syntax, we’re not getting any actors that have no films with
FILM_ID < 10. We should get dozens! How about this:
-- 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;
This used to produce the same result for an (
INNER)
JOIN, but given the
LEFT JOIN, we’re now not getting Susan Davis in the result:
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
The plans are also different:
---------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 |
| 1 | SORT ORDER BY | | 49 |
| 2 | HASH GROUP BY | | 49 |
|* 3 | HASH JOIN | | 49 |
|* 4 | INDEX FAST FULL SCAN| PK_FILM_ACTOR | 49 |
| 5 | VIEW | VW_GBF_7 | 200 |
| 6 | TABLE ACCESS FULL | ACTOR | 200 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ITEM_1"="FA"."ACTOR_ID")
4 - filter("FA"."FILM_ID"<10)
No outer join here! Versus
---------------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 |
| 1 | SORT ORDER BY | | 200 |
| 2 | MERGE JOIN OUTER | | 200 |
| 3 | TABLE ACCESS BY INDEX ROWID| ACTOR | 200 |
| 4 | INDEX FULL SCAN | PK_ACTOR | 200 |
|* 5 | SORT JOIN | | 44 |
| 6 | VIEW | VW_GBC_5 | 44 |
| 7 | HASH GROUP BY | | 44 |
|* 8 | INDEX FAST FULL SCAN | PK_FILM_ACTOR | 49 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."ACTOR_ID"="ITEM_1"(+))
filter("A"."ACTOR_ID"="ITEM_1"(+))
8 - filter("FILM_ID"(+)<10)
The first query did not produce an outer join operation, the second one did!
What’s the difference?
The difference is:
- An
INNER JOIN produces all the actors who played in at least one film, filtering out the actors who did not play in a film. That’s the very definition of an inner join. If we filter the films with FILM_ID < 10, that simply means we don’t want any actors without such films either.
- A
LEFT JOIN will produce all the rows from the left side of the join, regardless if there is a matching row on the right side of the join.
In both cases, the matching rows are determined by the
ON clause. If two rows don’t match, then:
- The
INNER JOIN removes them both from the result
- The
LEFT JOIN retains the left row in the result
But regardless what the
JOIN produces, the
WHERE clause will again remove rows that do not satisfy the filter. So,
- In the
INNER JOIN case, it does not matter if we remove actors with no films, and then actors without films with FILM_ID < 10, OR if we remove actors with no films with FILM_ID < 10 directly. They’re going to be removed anyway.
- In the
LEFT JOIN case, it does matter if we retain actors with no films, and then remove actors without films with FILM_ID < 10 (in case of which actors without films will be removed again), OR if we retain actors without films with FILM_ID < 10, and then not apply any further filters.
Conclusion
For
INNER JOIN,
WHERE predicates and
ON predicates have the
same effect.
For
OUTER JOIN,
WHERE predicates and
ON predicates have a
different effect.
In general, it is always best to put a predicate where it belongs,
logically. If the predicate is related to a
JOIN operation, it belongs in the
ON clause. If a predicate is related to a filter applied to the entire
FROM clause, it belongs in the
WHERE clause.
Like this:
Like Loading...
As a rule of a thumb, I put predicates that apply to the right side in the ON clause and predicates that apply to the left side in the WHERE clause.
I don’t think that rule of thumb works very well for larger join graphs that contain outer joins. The rule should really be: Does the predicate apply to the entire join graph / from table expression? Then it should be put in the WHERE clause. Does it apply to only an individual join operation, then it should be put in the ON clause.
Are there any implementation level optimisations done by different databases. From a query performance point of view, this has always confused me. And I for sure remember a where clause being faster than a predicate in the on clause in certain scenarios on Postgres. Is it possible that Postgres has some optimisations implemented for where predicates?
First off, the queries are either correct or wrong. Correctness is more important than any implementation specific performance considerations.
Of course, if you’re in the inner join case where the location of a predicate does not matter logically, so you might be able to benefit from an implementation specific “optimisation” – or rather flaw, because it really shouldn’t matter!
However, if you are sure you’re profiting from such a case, do add a comment to your query and verify if future versions of your database still behave this way, because they really shouldn’t. Also, I suggest sending a bug report to the vendor to fix any possible assymmetry.
Maybe I too am imagining things, but on SQL Server 2012 I have seen a number of cases where placement of the predicate affected execution/performance, typically with placement in the ON clause being the faster option.
Surely, some implementations must have been flawed in the past, but from a purely logical perspective, it would make no sense for one being faster than the other.
Do CTEs matter in this case? When i use the criteria in the ON in the CTE or in the WHERE when using the CTE?
I’m sorry, I don’t get your question
My understanding is that a predicate on the right relation after a left join is equivalent with an inner join unless the predicate allows for null-values.
Your examples seem to prove me wrong, unless the result sets you show are not complete. The sentence about Susan Davis not being in the result set any more seems to confirm this. She is not mentioned in any of the result sets in this post. Looking very closely we see that the example queries order the results in opposite directions.
I found this a bit confusing. :)
Maybe this helps?
It just produces:
I.e. the
FALSEpredicate will remove all rows from the right table prior to left joining them, but that still doesn’t affect the left table’s contribution to the join result. Remove the keywordLEFTto create anINNER JOIN, and you’ll see you won’t get any results at all.