HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Index performance on ON versus WHERE

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
indexwhereperformanceversus

Problem

I have two tables

@T1 TABLE
(
    Id INT,
    Date DATETIME
)

@T2 TABLE
(
    Id INT,
    Date DATETIME
)


These tables have a non-clustered index on (Id, Date)

And I join these tables

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON 
    t1.Id = t2.Id
WHERE 
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()


This can also be written as

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON 
    t1.Id = t2.Id
    AND
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()


My question is, which of these two queries gives the better performance and why? Or are they equal?

Solution

The performance will be the same. The optimizer will recognize this and create the same plan.

On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected.

For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query.

You should be able to determine the query plans for your own tables and data set so you can see what happens in your situation though.

SELECT * FROM salestable , custtable 
WHERE salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable 
JOIN  custtable 
ON salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable JOIN custtable 
ON salestable.custaccount = custtable.accountnum 
WHERE salestable.dataareaid = custtable.dataareaid


Gives these execution plans

Code Snippets

SELECT * FROM salestable , custtable 
WHERE salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable 
JOIN  custtable 
ON salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable JOIN custtable 
ON salestable.custaccount = custtable.accountnum 
WHERE salestable.dataareaid = custtable.dataareaid

Context

StackExchange Database Administrators Q#105525, answer score: 32

Revisions (0)

No revisions yet.