When does WHERE filtering occur?

Question

If I have a query such as this:

SELECT
    A.ID,
    A.Name,
    A.Type,
    B.FirstName,
    B.LastName,
    B.DateOfBirth,
    C.OfficeName
FROM A
    INNER JOIN B ON A.ContactID = B.ID
    INNER JOIN C ON B.OfficeID = C.ID
WHERE
    A.Type = 1

When does the A.Type = 1 filter get applied? Is it after the joins, or does the query look up 'A', determine whether it passes the filter, and then only join to B and C if it does?

Hope this makes sense. Thanks.

Can you include a query execution plan? That will likely answer your question. — mellamokb, Mar 04 '13 at 16:02
I was always under the impression that the query optimiser figured it out and applied it where it was most optimal (or near as). Run the query with the "Include Actual Query Plan" option turned on in SSMS to see what happens. — Colin Mackay, Mar 04 '13 at 16:03
Does it change anything? As long as results are correct of course. SQL Server will make it the way it's more efficient. — MarcinJuraszek, Mar 04 '13 at 16:06
@mellamokbtheWise - It was just something I was curious about. I just wondered if there were any solid rules that SQL followed. As is often the case, it seems the answer is 'it depends'. :) — Barguast, Mar 04 '13 at 16:09
A database systems textbook would cover this. Generally, `WHERE` conditions should be processed as early as possible since they reduce the size of the row set to be processed further. — millimoose, Mar 05 '13 at 01:51

John Woo · Accepted Answer · 2013-03-04T16:27:52.033

To start with, below is the SQL Order of Operations:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

In a simple query, the filtering happens after the FROM clause (joins are found on this part). What your querty above does is it primarily joins the tables with their linking columns that defines their relationship. After the records has been set (the result of joins) the WHERE clause then takes place to filter out Type where is is equal to 1.

Here's another example of using LEFT JOIN,

First Query:

SELECT  A.ID,
        A.Name,
        A.Type,
        B.FirstName,
        B.LastName,
        B.DateOfBirth
FROM    A
        LEFT JOIN B 
            ON  A.ContactID = B.ID AND
                B.LastName = 'Michaels'

vs Second Query:

SELECT  A.ID,
        A.Name,
        A.Type,
        B.FirstName,
        B.LastName,
        B.DateOfBirth
FROM    A
        LEFT JOIN B ON  A.ContactID = B.ID
WHERE   B.LastName = 'Michaels'

The first query returns ALL the records from table A. What B.LastName = 'Michaels' does is before the table B will be join with table A, it filters out all the records where the LastName is equal to Michaels. So the records from table A which do not have matches on the filtered records on Table B will have NULL values on the columns from Table B.

The second query will not yield the same result with the first query and performs exactly the same with INNER JOIN because after the records has been joined, another filtering will be executed on the result and takes only records where the LastName is equal to Michaels.

score 6 · Answer 2 · answered Mar 04 '13 at 16:03

6

Logically - after joins, physically - it's up to optimizer.

answered Mar 04 '13 at 16:03

a1ex07

36,826
12
90
103

score 4 · Answer 3 · answered Mar 04 '13 at 16:10

Per MSDN.
Check the section titled Logical Processing Order of the SELECT statement

FROM

ON

JOIN

WHERE

GROUP BY

WITH CUBE or WITH ROLLUP

HAVING

SELECT

DISTINCT

ORDER BY

TOP

There is an important note at the end of the paragraph preceding this list.

Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

score 2 · Answer 4 · answered Mar 04 '13 at 16:06

To answer this, you really need to look at the execution plan. In the case you mention, where you have a searchable argument (a SARG) in the where clause, the filter would most likely be applied with your access method, i.e. index, table scan, etc., before the join.

Use

Query Menu -> Include Actual Execution Plan

or

Query Menu -> Display Estimated Execution Plan

to take a look.

score 1 · Answer 5 · answered Mar 04 '13 at 16:12

1

Where clause will be applied before join i.e. it will return all the rows of A and corresponding rows from B and C depend upon join condition and for type = 1

answered Mar 04 '13 at 16:12

Abhishek Kumar

229
1
5

When does WHERE filtering occur?

5 Answers5

Linked