When does WHERE filtering occur? [duplicate]

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.

Answer 1

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.

Answer 2

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