SQL Server Performance
  1. atulgoswami New Member

    Normally we write Join like
    From Tab1 Inner Join Tab2 ON Tab1.PKUID = Tab2.FKUID Inner Join Tab3 ON Tab1.YPKMN_ID = Tab3.ZZYFKMN_ID
    But if write like
    From Tab1 Inner Join Tab2
    Inner Join Tab3
    ON Tab1.PKUID = Tab2.FKUID
    ON Tab1.YPKMN_ID = Tab3.ZZYFKMN_ID
    Is there any difference? Or by this would SQL interpretation and output be changed?
    Thanks
  2. Sandy New Member

    [quote user="atulgoswami"]Is there any difference? Or by this would SQL interpretation and output be changed?[/quote]
    No, in terms of result but may be you are changing your execution plan structure. But the same execution plan will generate but may be in different in structure. please note here you are only changing the sequence not any thing else, more over when we are using ANSI style join to make it more readable and its Microsoft Best practices like your 1st option.
    For Example:
    In the above example you can clearly analyze that what is your join type and on what condition you are joining the tables.
    Thank you,
    Sandy.
  3. atulgoswami New Member

    The example i gave is incorrect
    Msg 4104 Level 16 state 1
    The multipart identifier "" could not be found.
    I guess there should be some information on this and i believe it has some meaning.
    Thanks
  4. FrankKalis Moderator

    I guess in the case of INNER JOINs exclusively, the resultset is the same and the engine will figure that out. As soon as you introduce an OUTER JOIN things change and the output may be affected by how you write the query. But for readability and maintenance reasons I prefer your first version.
  5. FrankKalis Moderator

    [quote user="atulgoswami"]
    The example i gave is incorrect
    Msg 4104 Level 16 state 1
    The multipart identifier "" could not be found.
    I guess there should be some information on this and i believe it has some meaning.
    [/quote]
    Looks like you forgot to specify an identifier somewhere. If you can't figure it out yourself, just post the query.
  6. Sandy New Member

    Atul, Please check your code, you are missing something.
    [quote user="atulgoswami"]
    The example i gave is incorrect
    Msg 4104 Level 16 state 1
    The multipart identifier "" could not be found.
    I guess there should be some information on this and i believe it has some meaning.
    Thanks[/quote]
    I agree with Frank,
    [quote user="FrankKalis"] I guess in the case of INNER JOINs exclusively, the resultset is the same and the engine will figure that out. As soon as you introduce an OUTER JOIN things change and the output may be affected by how you write the query. But for readability and maintenance reasons I prefer your first version. [/quote]
    Thank you,
    Sandy.
  7. atulgoswami New Member

    Select <Column List>
    From
    Table1
    INNER JOIN Table2
    ON Table1.ID = Table2.ID
    INNER JOIN Table 3
    LEFT OUTER JOIN Table4
    ON Table3.ID = Table4.ID
    INNER JOIN Table5
    ON Table3.ID = Table5.ID
    ON Table2.ID = Table3.ID
    INNER JOIN Table6
    ON Table2.ID = Table6.ID
    I am just trying to understand this flow of joins. It would be of great help if mentor can put their thought on this.
    Thanks

Share This Page