Difference between filtering queries in JOIN and WHERE?

by

The answer is NO difference, but:

I will always prefer to do the following.

  • Always keep the Join Conditions in ON clause
  • Always put the filter’s in where clause

This makes the query more readable.

So I will use this query:

SELECT value
FROM table1
INNER JOIN table2
        ON table1.id = table2.id
WHERE table1.id = 1

However when you are using OUTER JOIN'S there is a big difference in keeping the filter in the ON condition and Where condition.

Logical Query Processing

The following list contains a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed.

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;

Flow diagram logical query processing

Enter image description here

  • (1) FROM: The FROM phase identifies the query’s source tables and
    processes table operators. Each table operator applies a series of
    sub phases. For example, the phases involved in a join are (1-J1)
    Cartesian product, (1-J2) ON Filter, (1-J3) Add Outer Rows. The FROM
    phase generates virtual table VT1.

  • (1-J1) Cartesian Product: This phase performs a Cartesian product
    (cross join) between the two tables involved in the table operator,
    generating VT1-J1.

  • (1-J2) ON Filter: This phase filters the rows from VT1-J1 based on
    the predicate that appears in the ON clause (<on_predicate>). Only
    rows for which the predicate evaluates to TRUE are inserted into
    VT1-J2.
  • (1-J3) Add Outer Rows: If OUTER JOIN is specified (as opposed to
    CROSS JOIN or INNER JOIN), rows from the preserved table or tables
    for which a match was not found are added to the rows from VT1-J2 as
    outer rows, generating VT1-J3.
  • (2) WHERE: This phase filters the rows from VT1 based on the
    predicate that appears in the WHERE clause (). Only
    rows for which the predicate evaluates to TRUE are inserted into VT2.
  • (3) GROUP BY: This phase arranges the rows from VT2 in groups based
    on the column list specified in the GROUP BY clause, generating VT3.
    Ultimately, there will be one result row per group.
  • (4) HAVING: This phase filters the groups from VT3 based on the
    predicate that appears in the HAVING clause (<having_predicate>).
    Only groups for which the predicate evaluates to TRUE are inserted
    into VT4.
  • (5) SELECT: This phase processes the elements in the SELECT clause,
    generating VT5.
  • (5-1) Evaluate Expressions: This phase evaluates the expressions in
    the SELECT list, generating VT5-1.
  • (5-2) DISTINCT: This phase removes duplicate rows from VT5-1,
    generating VT5-2.
  • (5-3) TOP: This phase filters the specified top number or percentage
    of rows from VT5-2 based on the logical ordering defined by the ORDER
    BY clause, generating the table VT5-3.
  • (6) ORDER BY: This phase sorts the rows from VT5-3 according to the
    column list specified in the ORDER BY clause, generating the cursor
    VC6.

it is referred from book “T-SQL Querying (Developer Reference)”

Leave a Comment