Inner joins

Question

Post reply

Inner joins

Viewing 5 posts - 16 through 19 (of 19 total)

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

andycadley SSCertifiable Points: 5295 More actions · Answer 1

andycadley

SSCertifiable

Points: 5295

November 25, 2018 at 2:48 pm

#2014455

Given that Microsoft seem to have taken to recommending the equi-join style when using graph tables and the MATCH operator, I think it's highly unlikely they're going away any time soon.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

andycadley - Sunday, November 25, 2018 2:48 PM

Given that Microsoft seem to have taken to recommending the equi-join style when using graph tables and the MATCH operator, I think it's highly unlikely they're going away any time soon.

MS' own code in SQL Server is riddled with old join syntax. E.g.
msdb.[dbo].[sp_DTA_table_access_helper_xml]:

select D.DatabaseID from
   [msdb].[dbo].[DTA_reports_querytable] as QT,
   [msdb].[dbo].[DTA_reports_table] as T,
   [msdb].[dbo].[DTA_reports_database] as D
   where
   QT.TableID = T.TableID and
   T.DatabaseID = D.DatabaseID and
   D.SessionID = @SessionID
   group by D.DatabaseID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

aaron.reese SSChampion Points: 13449 More actions · Answer 3

The one that freaks me out is the nested join syntax

SELECT A.something C.anotherThing FROM TableA AS A RIGHT JOIN ( TableB AS B INNER JOIN TableC AS C ON C.matchField=B.matchField ) ON B.matchToA = A.match

refactoring the code to remove the brackets and still keep the LEFTiness and RIGHTiness correct makes my brain bleed.
e.g. in the above code you will always return something from B where it matches C and something from A if it also matches B. the equivalent (proper) code would be

SELECT A.something C.anotherThing FROM TableB AS B INNER JOIN TableC AS C ON C.matchField=B.matchField LEFT JOIN TableA AS A ON A.match = B.matchToA

drew.allen SSC Guru Points: 76776 More actions · Answer 4

aaron.reese - Tuesday, November 27, 2018 6:12 AM

The one that freaks me out is the nested join syntax

SELECT A.something C.anotherThing FROM TableA AS A RIGHT JOIN ( TableB AS B INNER JOIN TableC AS C ON C.matchField=B.matchField ) ON B.matchToA = A.match

refactoring the code to remove the brackets and still keep the LEFTiness and RIGHTiness correct makes my brain bleed.
e.g. in the above code you will always return something from B where it matches C and something from A if it also matches B. the equivalent (proper) code would be

SELECT A.something C.anotherThing FROM TableB AS B INNER JOIN TableC AS C ON C.matchField=B.matchField LEFT JOIN TableA AS A ON A.match = B.matchToA

I actually find it fairly straightforward, but I'm sure that has a lot to do with my exposure to transformational syntax. It's also much easier than either quantum mechanics or lambda calculus.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

x SSC-Insane Points: 23660 More actions · Answer 5

Jeff Moden - Thursday, November 22, 2018 1:48 PM

drew.allen - Wednesday, November 21, 2018 11:33 AM

Jeff Moden - Wednesday, November 21, 2018 9:06 AM

ScottPletcher - Monday, November 19, 2018 11:57 AM

MS will probably get rid of the old-style joins at some point, but it will likely be a long while, since INNER joins cannot be misinterpreted in the WHERE clause like outer joins can.

Given that, I'd only do re-writes if you're touching the SQL for some other reason, not just to do the join change by itself.

Totally agreed on this. If they did get rid of "equi-joins" of this nature, it wouldn't be possible to execute correlated sub-queries. If they did such a thing, there'd probably be a mass march on Redmond.

As suggested above, I would take the time to change "equi-joins" to "ANSI-joins" as I encountered them in code.

HOGWASH. Just as English allows slightly different syntax in subordinate clauses, SQL can allow slightly different syntax for correlated sub-queries without causing problems. The semantics are clear.

Drew

Apparently my previous esponse was deleted. It's not HOGWASH. At least not when MS is concerned. If it were "easy" for them to drop support for "equi-joins" in a WHERE clause, they still might not have done it back when they dropped the ability to use them for outer joins. Considering how long it took them to drop *= and =* and the fact that "=" in equi-joins hasn't even made it to a deprecation list, yet, it'll be another 10 to 20 years before you even need to worry about it. Of course, they won't bother because they likely use the same code to do correlated subqueries and there's no real profit or marketing advantage to remove the ability to do "equi-joins" in the WHERE clause.

That doesn't really make (immediate) sense to me as syntactically, the sql 92 joins look quite different than correlated subqueries. With the sql 92 joins, it seems like you have an explicit list of table expressions after the "from" keyword, and all Microsoft has to do is to disallow that list with their parser, and any number of correlated subqueries would still be allowed in the syntax. With 92 joins, the hard work in my opinion was always examining the expression in the "where" clause to figure which tables in the "from" list participated in which "join" operations. The syntax was always a fairly straight forward parse in my opinion.

Off hand I can't think of any other non "92 style join" constructs that eliminating comma separated lists of table expressions (including derived tables) from the syntax would prevent. Happy to be shown any counter examples!