CODEWITHSUNDEEP
t-sql
agnieszka
agnieszka

Reputation: 15345

Does filtering or joining happen first in T-SQL?

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?

Upvotes: 7

Views: 2543

Answers (4)

A-K
A-K

Reputation: 17090

When the query is exercised as a merge join, filtering and joining occur simultaneously.

Upvotes: 0

Tomalak
Tomalak

Reputation: 338228

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.

Upvotes: 1

Fenton
Fenton

Reputation: 250942

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.

Upvotes: 0

Michael Haren
Michael Haren

Reputation: 108276

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.

Upvotes: 16

Related Questions