CODEWITHSUNDEEP
sqlsql-server
SQLBrickey
SQLBrickey

Reputation: 11

JOIN inside WHERE vs outside WHERE

Will both statements return the same results?

The top code has the JOIN in the WHERE clause and takes over 4 hrs to run. By moving the JOIN outside the WHERE the same query runs in 1m 6s. I have not seen a JOIN in a WHERE clause.

Note DSA.doc_id is a child version record of orders.folder.

    --OLD CODE TAKING 4+ HRS TO RUN
    SELECT top 1 status_to_date
    FROM DSA
    WHERE (status_to = 'CA') 
    and left(DSA.doc_id,12) = orders.folder 


    --NEW CODE TAKING LESS THAN A MINUTE
    SELECT top 1 status_to_date
    FROM DSA
    left outer join orders 
        on left(DSA.doc_id,12) = orders.folder 
    WHERE (status_to = 'CA')

Upvotes: 1

Views: 1231

Answers (1)

Rizwan Gill
Rizwan Gill

Reputation: 2253

Yes I believe both queries will give same result. And in side where join gives slowest result. But there is one more alternate of above query which willl be nmore and more faster then second query too

Select 
     Top (1) status_to_date
 FROM 
    DSA
 WHERE 
    (status_to = 'CA')
    AND  (Select count(*) From orders where orders.folder =  left(DSA.doc_id,12)) > 0

Upvotes: 2

Related Questions