tag:blogger.com,1999:blog-2982650383370319756.post2462137786691643093..comments2022-10-08T11:25:21.546-04:00Comments on Dev's Guide to SQL Server: What kind of JOIN syntax is this?Vladimir Oselskyhttp://www.blogger.com/profile/09985820247244854091noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-2982650383370319756.post-58341980193332036442017-03-27T15:54:24.234-04:002017-03-27T15:54:24.234-04:00I would call it a form of structured or nested que...I would call it a form of structured or nested query. <br />https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql shows that a "joined table" can be like a Russian doll of joined tables within joined tables.<br />It is not obvious or intuitive, I prefer to expand things like this as common table expressions which help me build up my understanding of the nested constructs Recombinant Sockshttps://www.blogger.com/profile/10981573106238238007noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-18608270840616420972017-03-27T11:28:59.474-04:002017-03-27T11:28:59.474-04:00That's the "syntax" I teach and use ...That's the "syntax" I teach and use (I'm an instructor). It's a consecuencuence of following the model. After joining two tables you can treat the resultset as another table so this style results naturally when we introduce outer joins.<br />I would write the query like this to explain it:<br /><br />SELECT t.*,<br /> s.*<br />FROM Teacher AS t<br /> LEFT JOIN (<br />Anonymoushttps://www.blogger.com/profile/08285266975793843585noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-39058335982043936952017-03-27T10:11:11.225-04:002017-03-27T10:11:11.225-04:00In other word SQL JOIN and ON statements are not r...In other word SQL JOIN and ON statements are not required to be adjacent. Both examples show that. IMHO it's better practice, as Vlad noted, to place the ON next to the JOIN whose table it references.Anonymoushttps://www.blogger.com/profile/13637519736430675977noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-59919542675764361522017-03-27T09:03:45.621-04:002017-03-27T09:03:45.621-04:00Nice find Mr. OsselskyNice find Mr. OsselskyBrianHhttps://www.blogger.com/profile/16519129131020132021noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-63376053736706424322017-03-27T03:34:53.033-04:002017-03-27T03:34:53.033-04:00I think this join syntax is called a chiastic rela...I think this join syntax is called a chiastic relationship and is described by Itzik Ben-Gan in the book Inside Microsoft SQL Server 2008 T-SQL Querying: T-SQL QueryingTSQLrangerhttps://www.blogger.com/profile/06515911276659252533noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-10635935405764642552017-03-27T03:24:41.256-04:002017-03-27T03:24:41.256-04:00I think this join syntax is called a chiastic join...I think this join syntax is called a chiastic join, as you mention if you need to nest a join with another inner table before joining to the outer table you can use this method.<br />Itzik Ben-Gan describes this join in the book Inside Microsoft SQL Server 2008 T_SQL Querying: T-SQL Querying <br />TSQLrangerhttps://www.blogger.com/profile/06515911276659252533noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-89171225050476396572017-03-26T01:53:47.259-04:002017-03-26T01:53:47.259-04:00The joins happen based on the order of the ON stat...The joins happen based on the order of the ON statements not the join statements. Student is first joined to grades (first ON statement) and then the results are left joined to teacher (second on statement).<br /><br />It could also be written as:<br /><br />SELECT t.*<br /> , s.*<br />FROM dbo.Student AS s<br />JOIN dbo.Grades AS gr<br /> ON s.ID = gr.StudentID<br /> AND Anonymoushttps://www.blogger.com/profile/00113926119039227591noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-36552130063096991202017-03-25T21:09:41.080-04:002017-03-25T21:09:41.080-04:00In the old days, the optimizer gave up beyond a ce...In the old days, the optimizer gave up beyond a certain point, so you had to optimize the plan your self. Try this query syntax with option force order, or any join hint, then switch around the join and ON clauses, see how it produces different joins orders.Anonymoushttps://www.blogger.com/profile/15233400501511753581noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-63916801539810344162017-03-25T19:58:39.455-04:002017-03-25T19:58:39.455-04:00When we created the in fixed join notation, we wer...When we created the in fixed join notation, we were basing it on some notes from Chris Date in an article in one of the database magazines at the time. He never actually propose the syntax; in fact, neither he nor Dr. Codd ever sent anything into ANSI X3H2 for consideration. <br /><br />Our rules were that the ON clauses associate with the nearest join (assuming the particular join could take an --CELKO--https://www.blogger.com/profile/11908190660799274693noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-16819834786466842692017-03-25T17:42:58.737-04:002017-03-25T17:42:58.737-04:00You see it more often in generated queries. I wrot...You see it more often in generated queries. I wrote about it in one of the "MVP Deep Dives" books, including a description of how to read it. That is, to find the first ON, and then work backwards from there to find the last (unmatched) JOIN. Everything between the two is the right side of the join (so ident, put parentheses, whatever). Then keep looking backwards until you reach Rob Farleyhttps://www.blogger.com/profile/05378118082084726661noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-89927686280563939032017-03-25T11:36:32.250-04:002017-03-25T11:36:32.250-04:00I have inherited a lot of legacy code that is writ...I have inherited a lot of legacy code that is written this way. There are lots of filtering subqueries etc. Fortunately for me there are not a lot of select * in these, so you are able to determine WTH they were trying to do. Cryptic but functional, I do prefer temp tables when possible as not to stress out memory and cache. Thanks for shedding the light, at least I know I am not alone in thisEric S Blakehttps://www.blogger.com/profile/18092704475580587858noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-74283851397230120542017-03-25T10:29:34.149-04:002017-03-25T10:29:34.149-04:00Hi! Like you I saw a similar code time ago and I h...Hi! Like you I saw a similar code time ago and I had the same impressions regarding the script was wrong (but it wasn't). After google a little I found that this approach seems to change the execution orden in joins, which sometimes it will help you for performance issues.<br />Mauricio Anonymoushttps://www.blogger.com/profile/06994496105588333607noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-26022418708925023602017-03-25T10:16:04.078-04:002017-03-25T10:16:04.078-04:00It's called "Logical Join Evaluation Orde...It's called "Logical Join Evaluation Order". You can find explanation and example in "Inside Microsoft SQL Server 2008: T-SQL Querying" on page 408.Anonymoushttps://www.blogger.com/profile/07574178838924802798noreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-50292003918041276842017-03-25T10:04:06.375-04:002017-03-25T10:04:06.375-04:00Randomly, this JOIN syntax causes the TransactSql ...Randomly, this JOIN syntax causes the TransactSql ScriptDom to parse much more slowly - so if you're doing any sort of AST based analysis to walk dependencies - then this syntax actually makes that a lot more processor intensive as well.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2982650383370319756.post-52131959128838893572017-03-25T08:24:11.497-04:002017-03-25T08:24:11.497-04:00Oh Vlad, there are many special names for this ste...Oh Vlad, there are many special names for this steaming pile of ridiculous hyper-optimization and intentional obfuscation that I can think of. The real problem, which you have correctly identified, is the complete antipathy of the author to easy understanding and maintainability.Allenhttps://www.blogger.com/profile/02159154128242025631noreply@blogger.com