Filters in the JOIN Clause vs. WHERE Clause: Does It Matter?

Here’s a question I’ve been asked multiple times from multiple people: “Does it matter I put filters in the join clause vs in the WHERE clause?  And if so, which one is better?”

Example:

select *
from table_a a
      inner join table_b b on (a.id = b.id and b.some_column = 'X')

vs.

select *
from table_a a
     inner join table_b b on (a.id = b.id)
where b.some_column = 'X'

So does it matter?

Answer: No…and Yes.

When it Doesn’t (or at least Shouldn’t) Matter

“No” it doesn’t matter, in the sense that if you have a inner join between two different tables, and you put your filter criteria in the join portion, Oracle should do the same thing either way.

I say “should”…sometimes, things that “should” be true in a database, aren’t.

For example, it shouldn’t matter what order you list your tables in the FROM clause, right? 

Well, the truth is I’ve seen instances where it absolutely does matter what order you list tables in within a FROM clause and listing the tables in different orders will yield different plans (and yes, to specify, I’m talking queries that don’t have an  /*+ ordered */ hint).

I’ve never seen a situation where adding a filter in the WHERE clause vs. in the JOIN clause on an inner join makes any difference…but to know for sure, you would want to check out the execution plan of both queries (including the filters), and compare them.

When It Does Matter

Suppose what you have is an OUTER join, not an inner join….putting the filter in the JOIN criteria will often yield a totally different result.

E.g.

select *
from table_a a
       left outer join table_b on (a.id = b.id and b.some_column = 'X')

…is totally different than…

select * 
from table_a a       
     left outer join table_b on (a.id = b.id) 
where b.some_column = 'X'

Those two queries are extremely different.

The first query says “take all the recods in TABLE_A and try to join them to TABLE_B if they meet the SOME_COLUMN = ‘X’ criteria, otherwise return an “all nulls” value for the record from TABLE_B.

The second query isn’t really an outer join at all. 

The filter SOME_COLUMN = ‘X’ is applied after the two tables are joined, any “all null” records in TABLE_B will be discarded as a result of the filter, and what was probably supposed to be an outer join is, as a result, effectively converted to an INNER JOIN.

Main point: If you have suspicions that they might be different, look at the execution plan and the filters, and verify those aren’t different. 

If those are the same, you can probably expect similar performance.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets