none
Which one of using Left join or using CTE is better in this instance query?

    Question

  • Hello everybody

    i doubt to use either left join or combination of CTE and Left Join for below Query.

    which one is better in your opinion and why?

    i know there is no difference between these two query in terms of performance but i want to be sure about Readability differences.

    First code using Left Join:

    select * 
    from dbo.City C
    left join dbo.Location L on  L.City = C.Id and L.Active = 1

    Second code using CTE:

    with ActiveLocation as
    (
    	select *
    	from dbo.Location
    	where Active = 1
    )
    select * 
    from dbo.City C
    left join ActiveLocation A  on A.City = C.Id

    Both of them have same result. but there is a difference in allocation of Active clause.

    Thanks a lot.


    Saturday, January 12, 2019 7:20 AM

All replies

  • Good day,

    As a start point for performance discussion, Please provide the table DDL (including indexes) and the Execution Plan which you get for each query. In addition SET the STATISTICS IO and TIME to ON (each execution only one of them) and provide the information you get when you execute the query, then post it and we will be able to answer your question

    * If you do not know how to provide any of these information please say so and we will explain how to get it :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Saturday, January 12, 2019 8:54 AM
    Moderator
  • I would say that it is a matter of taste which you prefer. I would prefer the first in this simple example. But say that the condition  for Location is a lot more complicated. In that case I might prefer to use a CTE or a derived table.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 12, 2019 10:02 AM
  • thanks for the answer

    The tables have created just for the issue and they are two simple table with no indexes except Id field. Also the execution plan gives same percent as cost.

    Eagerly i want to know which of them is best pattern for teaching SQL Query. as my experience there are disagreement between senior developers about such queries.

    Saturday, January 12, 2019 11:15 AM
  • so as i understood, you believe that it should to use first sample  in simple cases and when Location clause get more complicated it's better to use CTE.

    if that's true then i agree with you. CTE can explain logic better than complicated Left Join.

    thanks.

    Saturday, January 12, 2019 11:22 AM
  • so as i understood, you believe that it should to use first sample  in simple cases and when Location clause get more complicated it's better to use CTE.

    if that's true then i agree with you. CTE can explain logic better than complicated Left Join.

    thanks.

    Hi,

    I don't agree that you should select specific solution over the other if you have 2 solutions which each might fit better for different scenario, unless the scenario is perfectly clear and there is a VERY GOOD reason for it. Moreover, if you do want to present solution during teaching, then this become more important! You must make the listeners understand that you have several solutions, and that they must choose a solution case-to-case according to their specific scenario and after they checked the Execution Plan, the IO statistics, and the TIME statistics.

    Doing so, will probably lead people like me to HIGHLY NOT RECOMMEND YOUR TEACHING and to explaining that this is a poor teaching level and a good example why they should pay a bit more (or pay none and use the internet) and get a course from someone who understand what he teach :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Saturday, January 12, 2019 8:02 PM
    Moderator
  • so as i understood, you believe that it should to use first sample  in simple cases and when Location clause get more complicated it's better to use CTE.

    if that's true then i agree with you. CTE can explain logic better than complicated Left Join.

    thanks.

    Generically when you want to modularize the functionality into sub sections and implemented complicated set of conditions within, then its best to use CTE as it improves clarity

    From a performance stand point you have to evaluate both option based on your case before you settle for a method. Not all times you can say one method will outplay the other


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 13, 2019 6:43 AM
  • Hi,

    I believe for the performance it depend on many factors e.g. as mentioned the indexes.

    But for readability as of personal opinion I think it is better to use the left out join as it is simplify the query and also it is one of the basic concept in database. I know for many people CTE is something complicated and normally advance users are using it. Meaning you can also think about who will use the query (and maybe modify it) after you or in your absence.

    I hope this is helpful


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Sunday, January 13, 2019 7:41 AM
  • Hi Dariush_Malek,

     

    As you said that 'there is no difference between these two query in terms of performance', so I think in your simple example LEFT JOIN and CTE might have the same performance.

     

    I think the biggest benefit for using CTEs is readability. It makes it much easier to see what queries are being used as subqueries, and then it's easy to join them into a query. However, in your two scripts, using CTE is redundant and it makes your script become complicated.  So I think you can use LEFT JOIN in this example .

     

    By the way, if you would like to know which one has a better performance between LEFT JOIN and CTE. It might be hard to say because the performance is related to your real data , your indexes or some other environment factors.  Please try to compare their performance against the actual execution plan. Also, you can 'SET STATISTICS IO ON; ' to display information regarding the amount of disk activity generated. Please compare the messages and you will see the difference. For more details , you can refer to it: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 14, 2019 2:49 AM