Left outer Join query optimization

Question

I am not so advanced sql user. Could you please review my following query is it optimal ? or I can do something more more optimized and more readable?

      select  Distinct  DT.Station  , DT.Slot , DT.SubSlot, DT.CompID , CL.CompName
            from    (
                        select  Station, Slot, SubSlot, CompID
                        from    DeTrace
                        where   DeviceID = '1151579773'     
                    ) as DT 
            Left outer CList as CL  
                  on  DT.CompID = CL.CompID 
                  where CL.CompName = '9234220'
                  order by CompName

Thanks for help.

Klas Lindbäck · Accepted Answer · 2013-05-22 10:30:11Z

2

It is easier to read like this:

 select  Distinct  DT.Station  , DT.Slot , DT.SubSlot, DT.CompID , CL.CompName
            from  DeTrace DT    
            Left outer join CList as CL  
                  on  DT.CompID = CL.CompID 
                  where CL.CompName = '9234220'
                    and DT.DeviceID = '1151579773'
                  order by CompName

The optimiser should be able to perform this query as efficiently as yours, but you should check the query execution plan just to be sure.

answered May 22, 2013 at 10:30

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Schalk · Accepted Answer · 2013-05-22 10:29:28Z

0

Why not just:

SELECT DISTINCT DT.Station  , DT.Slot , DT.SubSlot, DT.CompID , CL.CompName
FROM DeTrace DT
LEFT OUTER JOIN CList CL ON DT.CompID = CL.CompID
                       AND DT.DeviceID = '1151579773'
                       AND CL.CompName = '9234220'
ORDER BY CL.CompName

answered May 22, 2013 at 10:29

Schalk

1425 bronze badges

Comments

Nilesh Thakkar · Accepted Answer · 2013-05-22 10:30:42Z

0

I think you don't need subquery. Check below:

select  Distinct  DT.Station  , DT.Slot , DT.SubSlot, DT.CompID , CL.CompName            
from    DeTrace as DT 
Left outer CList as CL  
on  DT.CompID = CL.CompID 
where CL.CompName = '9234220' and DeviceID = '1151579773'     
            order by CompName

answered May 22, 2013 at 10:30

Nilesh Thakkar

2,8951 gold badge25 silver badges44 bronze badges

2 Comments

Schalk Over a year ago

If you use a WHERE clause in a join, it first joins ALL the records and then filter the result. If you have the condition IN the join, then it first filters before joining all the records

Nilesh Thakkar Over a year ago

I agree with you. But it may not return expected output and hence it's always advisable to put search predicate in where clause when using Outer Join as shown here

Christer Backlund Høllesli · Accepted Answer · 2013-05-22 10:34:07Z

0

I like this way best:

SELECT  DISTINCT
        DeTrace.Station,
        DeTrace.Slot,
        DeTrace.SubSlot,
        DeTrace.CompID,
        CL.CompName
    FROM
        DeTrace
    LEFT OUTER JOIN
        CList AS CL ON
            DeTrace.CompID = CL.CompID
    WHERE
        DeviceID = '1151579773' AND
        CL.CompName = '9234220'
    ORDER BY
        CL.CompName

answered May 22, 2013 at 10:34

Christer Backlund Høllesli

3091 silver badge4 bronze badges

Comments

Marius · Accepted Answer · 2013-05-22 10:34:38Z

0

select  Distinct  DT.Station  , DT.Slot , DT.SubSlot, DT.CompID , CL.CompName                  
from    DeTrace DT
Left outer join CList as CL  
          on  DT.CompID = CL.CompID 
where DT.DeviceID = '1151579773' 
and  CL.CompName = '9234220'    
order by CL.CompName

The Sql Server Cost Based Optimizer should be able to determine the most efficient order in which to apply the conditions.

answered May 22, 2013 at 10:34

Marius

235 bronze badges

Collectives™ on Stack Overflow

Left outer Join query optimization

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related