10

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
Night Walker
  • 20,638
  • 52
  • 151
  • 228
  • 1
    That's not how SQL syntax works. And assuming that the clauses run the way your write them is... simplistic at best. The filtering may very well happen before the join condition (if the query optimizer is any good and the statistics point it at that dirction). – Oded May 19 '13 at 09:08
  • 1
    Night Walker: If you have performance problems (ex. you have a `Filter` operator after `Nested Loop/Hash/Merge Join`) with this query you should post the execution plan and DDL statements (for tables and indices) within another question after closing current question. – Bogdan Sahlean May 19 '13 at 09:59
  • It seems like advanced users are reading this to be about query optimizer details. But it looks like the OP just wants to filter on DeviceID. Not sure how that can be "not constructive". – Andomar May 19 '13 at 14:43
  • @NightWalker Can you clarify the question? What is it that you need and why you need it? (is it a performance problem, a code elegance problem, something else ...?) As it stands, it's not very clear and this leads to contradiction and extended discussion. – ypercubeᵀᴹ May 19 '13 at 19:35
  • It "seems" that SELECT's syntax (`SELECT ... FROM ... WHERE ...`) and logical execution order (`FROM/"joins" > ON, WHERE, GROUP BY, ...`) cannot be changed. – Bogdan Sahlean May 19 '13 at 20:40

5 Answers5

12

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.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    might be off topic but might help you differentiate between adding the condition in the `ON` clause and on the `WHERE` clause. [**CLICK here**](http://stackoverflow.com/questions/15706112/why-and-when-a-left-join-with-condition-in-where-clause-is-not-equivalent-to-the) – John Woo May 19 '13 at 08:57
11

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).

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • 3
    100% agree! You shouldn't do it; optimizer will do it automatically. Because you asked this question I belive you are not going to do some manual optimization of your request with hints and other advanced staff. Just to make it clear your request must be select Distinct Station , Slot , SubSlot, CompID , CompName from DeviceTrace as DT inner join CompList as CL where and DT.CompID = CL.CompID and DT.DeviceID = '1339759958' – Roman Podlinov May 19 '13 at 09:02
4

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    -1 Fallacy. It does not "apply the WHERE clause before the JOIN". SQL Server sees through that subquery as easily as a query in a JOINed view. – RichardTheKiwi May 19 '13 at 08:59
  • @RichardTheKiwi: The optimizer can rewrite a query to an equivalent other query. As noted in the bottom half of the answer I agree that those are equivalent. Not sure how that makes this answer fallacious. – Andomar May 19 '13 at 09:10
  • The answer ("apply") implies that the user can use the code provided to *effect* a WHERE evaluation before a JOIN/ON. You can certainly write it that way, but it's a fallacy that it will be applied first. Also the note underneath is ambiguous as to whether you're only clarifying that the "resulting rows will be the same". – RichardTheKiwi May 19 '13 at 09:13
  • Sounds like you're reading the term "apply" to mean "force the optimizer to execute a query in the following way". I'm not sure that's the most obvious reading in this context ;) – Andomar May 19 '13 at 09:27
  • I don't suppose my English is the best in the world, but when you have a question *context*, and the answer stating "You **can** use.. to apply.. where.. before.. join" as an answer. You know :) – RichardTheKiwi May 19 '13 at 09:41
  • @RichardTheKiwi: Reading the question, the OP is looking for any correct way to formulate his reporting question. There is no hint that the OP is interested in how the optimizer executes that query. – Andomar May 19 '13 at 09:49
1

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'
Roman Podlinov
  • 23,806
  • 7
  • 41
  • 60
0

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
Fauzan Samsuri
  • 25
  • 5