Does filtering or joining happen first in T-SQL?

Question

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?

Answer 1

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.

Answer 2

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.