I have two tables:
Customers(Id, Name, TownId) T
Towns(Id, Name)
I have an SQL statement like this:
SELECT *
FROM Customers
INNER JOIN Towns ON Towns.Id = Customers.TownId
WHERE Customers.Id > 5
What would happen first?
Would it filter the Customers table and then join the selected records with Towns table?
Would it join all Customers with Towns and then filter? Or is it that you cannot tell?
The optimizer will do whatever it thinks will be fastest.
You can force certain behaviors with join hints or encourage certain behaviors with statistics and indexes. It's usually best to Trust the Optimizer, though.
If you want a detailed explanation of how a query is executed, look at the execution plan.
In laymans terms, the filter will happen before the join, so you won't join the entirety of both tables before filtering.
Depending on the toolset you are using, you should find an option to show the execution plan of the query, which will help you to see the order of execution and the cost of each stage.
Generally speaking, joining happens first. Consider:
t1.id t1.foo t2.id t2.bar t2.t1_id
------------- ------------------------
1 'abc' 1 '123' 1
2 'def' 2 '456' 1
3 'ghi' 3 '789' 2
4 'jkl' 4 '0' NULL
This query:
SELECT
t1.foo,
t2.bar
FROM
t1
LEFT JOIN t2 ON t1.id = t2.t1_id
WHERE
t2.somevalue IS NOT NULL
will yield:
foo bar ------------- 'abc' '123' 'abc' '456' 'def' '789'
whereas, when you pull the filter into the join condition, filtering happens at the join as well:
SELECT
t1.foo,
t2.bar
FROM
t1
LEFT JOIN t2 ON t1.id = t2.t1_id AND t2.somevalue IS NOT NULL
will yield:
foo bar ------------- 'abc' '123' 'abc' '456' 'def' '789' 'ghi' NULL 'jkl' NULL
The more complex the query gets, the less easy it is to say which records are filtered out by the execution plan before the tables are joined, and which after that.
When the query is exercised as a merge join, filtering and joining occur simultaneously.