CODEWITHSUNDEEP
sqlsql-serverinner-join
Night Walker
Night Walker

Reputation: 21260

How to do WHERE clause BEFORE INNER JOIN

How I do query like this ?

select Distinct  Station  , Slot , SubSlot, CompID , CompName 
from DeviceTrace as DT DT.DeviceID = '1339759958' 
inner join CompList as CL  
where  and DT.CompID = CL.CompID

I need to do DT.DeviceID = '1339759958' before I start with the inner join. I work with sql server.

Upvotes: 12

Views: 64349

Answers (5)

Fauzan Samsuri
Fauzan Samsuri

Reputation: 25

You can try this :

select Distinct Station, Slot, SubSlot, CompID, CompName 
from (select * from DeviceTrace where DeviceID = '1339759958') as DT  
inner join CompList as CL ON DT.CompID = CL.CompID

Upvotes: 0

Roman Podlinov
Roman Podlinov

Reputation: 24944

A small clarification to answer from David Aldridge. You must use query 

select Distinct  Station  , Slot , SubSlot, CompID , CompName 
from DeviceTrace as DT 
inner join CompList as CL on DT.CompID = CL.CompID  
where DT.DeviceID = '1339759958'

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52346

I find it difficult to believe that it makes any difference. The query optimiser should apply the predicate before the join if it calculates that it is more efficient to do so. The only circumstance where you might need to do this is when the optimiser makes an erroneous choice (for inner joins at least -- there are valid cases for outer joins).

Upvotes: 12

Andomar
Andomar

Reputation: 238078

You can use a subquery to apply a where clause before a join:

select  *
from    (
        select  *
        from    DeviceTrace
        where   DeviceID = '1339759958' 
        ) as DT 
inner join 
        CompList as CL  
on      DT.CompID = CL.CompID

Although in this case, it should not matter whether you filter in a subquery, the on clause, or even the final where.

Upvotes: 4

John Woo
John Woo

Reputation: 263703

try adding in ON clause.

SELECT DISTNCT Station, Slot, SubSlot, CompID, CompName 
FROM   DeviceTrace AS DT INNER JOIN CompList AS CL 
        ON  DT.CompID = CL.CompID AND
            DT.DeviceID = '1339759958'

In this case, the result is the same since you are doing INNER JOIN. Adding the condition in the ON clause can be very different when doing LEFT JOIN and filtering on the right hand side table.

Upvotes: 13

Related Questions