SQL Server Optimization – Difference Between Filtering in ON Clause vs WHERE Clause

optimizationperformancequeryquery-performancesql-server-2016t-sql

I have two tables with these structures:

Table1(Date ,Code ,Name)
Table2(Date ,Code)

I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table.I want to know whether there are differences between these two queries in terms of the final result and query performance. In the First query I'm filtering dates in ON clause and in the second query I'm filtering dates in the where clause.
Will there be any condition that these two queries produce different result set?
Thanks in advance.

query1 :

      Select "Desired Columns"
      From Table1 T1 Inner Join Table2 T2
      On T1.Date = "The specific date" and
         T2.Date = "The specific date" and
         T1.Code = T2.Code

query2: 

      Select "Desired Columns"
      From Table1 T1 Inner Join Table2 T2
      On T1.Code = T2.Code
      where T1.date ="The specific date" and T2.Date = "The specific date"

Best Answer

Will there be any condition that these two queries produce different result set?

Logically, both queries must produce same result set, but deepening on Query Optimizer and Logical Processing Order both queries may produce different work-load (CPU Worker time, Logical Reads etc..) due to following condition:

  • Query 1:
    Retrieves the filtered rows based on ON clause FROM tables, renders as per SELECT clause
  • Query 2:
    Retrieves the rows FROM tables, get filtered based on WHERE clause and renders as per SELECT clause

Following query can help to see the stats of the both queries. You could also have IO insights by setting SET STATISTICS IO ON

SELECT  t.[text] as sql_Text, 
        s.total_elapsed_time, 
        ((s.total_elapsed_time + 0.0) /s.execution_count) as avg_elapsed_time,
        s.total_worker_time, 
        ((s.total_worker_time + 0.0) / s.execution_count) as avg_worker_time,
        s.total_clr_time
FROM sys.dm_exec_query_stats AS s 
        CROSS APPLY sys.dm_exec_sql_text(s.[sql_handle]) AS t
WHERE t.[text] LIKE N'%From Table1 T1 Inner Join Table2 T2%'
ORDER BY s.last_execution_time;
Related Question