Post reply

Converting A OUTER APPLY to LEFT JOIN in a query

  • October 10, 2014 at 3:47 am

    #290827

    Hi

    I need to convert a OUTER APPLY hint in my query to LEFT JOIN.

    How it can be done?

    The code which is presently is this:

    OUTER APPLY AdditionalFields.nodes('/AdditionalFields/AdditionalField') AS AF (C)

  • October 10, 2014 at 3:55 am

    #1751537

    Why?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 10, 2014 at 4:00 am

    #1751539

    I am now using a SELECT DISTINCT to avoid the duplicate records from retrieving.

    I can remove this DISTINCT keyword from the query once the OUTER APLLY is changed to LEFT JOIN

  • October 10, 2014 at 4:19 am

    #1751546

    Junglee_George (10/10/2014)

    I am now using a SELECT DISTINCT to avoid the duplicate records from retrieving.

    I can remove this DISTINCT keyword from the query once the OUTER APLLY is changed to LEFT JOIN

    Yes, but the OUTER APPLY is calling a function (the XML .nodes function). I don't think you can call functions in a LEFT JOIN directly.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 11, 2014 at 12:12 pm

    #1751823

    Junglee_George (10/10/2014)

    I am now using a SELECT DISTINCT to avoid the duplicate records from retrieving.

    I can remove this DISTINCT keyword from the query once the OUTER APLLY is changed to LEFT JOIN

    Quick question, on what do you base that assumption?

    😎

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply