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