November 19, 2018 at 11:17 am
Hi all.
Our current version is 2008 and we are going to migrate it to 2016 or 2017.
In many stored procedures, we have old-style inner joins, like:
SELECT ...
FROM table1 t1, table2 t2
WHERE t1.c1 = t2.c2
Does it make a sense to re-write them to a syntax like:
SELECT ...
FROM table1 t1 join table2 t2
ON t1.c1 = t2.c2
?
Does Microsoft have in its plans to drop old-style joins in future versions?
Thanks
November 19, 2018 at 11:54 am
Some parts of the ANSI-89 joins aren't deprecated/unsupported no, but others are (you can't use the LEFT JOIN syntax anymore, for example) Does it make sense to rewrite them? Yes, that is the ANSI-92 syntax, so has been around for over 25 years; why are you not making use of it?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 19, 2018 at 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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 19, 2018 at 12:53 pm
Thanks Thom and Scott.
Yes, I am fully aware that we can't use old-style (left or right) OUTER joins for a long time, probably since 2005.
And yes, we are changing them in our SQL code only if we make other changes to a S.P. or a query, but our migration is looming so I wanted to be sure that we are safe, for now.
Thanks
November 21, 2018 at 8:18 am
SQL Guy 1 - Monday, November 19, 2018 12:53 PMThanks Thom and Scott.Yes, I am fully aware that we can't use old-style (left or right) OUTER joins for a long time, probably since 2005.
And yes, we are changing them in our SQL code only if we make other changes to a S.P. or a query, but our migration is looming so I wanted to be sure that we are safe, for now.
Thanks
The Database Upgrade Advisor can be used to find code incompatibilities but the only way you can be safe is to actually test. Until you stand up a instance of 2016 or 2017, migrate a copy of your database(s) and start executing code, you won't have any assurance of a safe transition.
November 21, 2018 at 9:06 am
ScottPletcher - Monday, November 19, 2018 11:57 AMMS 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2018 at 11:33 am
Jeff Moden - Wednesday, November 21, 2018 9:06 AMScottPletcher - Monday, November 19, 2018 11:57 AMMS 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 21, 2018 at 12:39 pm
drew.allen - Wednesday, November 21, 2018 11:33 AMJeff Moden - Wednesday, November 21, 2018 9:06 AMScottPletcher - Monday, November 19, 2018 11:57 AMMS 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
Yeah, I was thinking more of them banning "FROM a, b" rather than trying to ban anything in the WHERE clause itself. Still, I think it will a very long time before they actually ban that, because (1) there'd be a lot of blowback from it and (2) equi-joins in the WHERE aren't an issue anyway, and they've already dealt with the genuine problem of outer joins in the WHERE.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 21, 2018 at 1:03 pm
drew.allen - Wednesday, November 21, 2018 11:33 AMJeff Moden - Wednesday, November 21, 2018 9:06 AMScottPletcher - Monday, November 19, 2018 11:57 AMMS 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
Heh... I'll see your HOGWASH and raise you one BULLSH1T! MS won't make the differentiation. The prof there is that they didn't make the change when they changed the outer joins. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2018 at 1:48 pm
drew.allen - Wednesday, November 21, 2018 11:33 AMJeff Moden - Wednesday, November 21, 2018 9:06 AMScottPletcher - Monday, November 19, 2018 11:57 AMMS 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2018 at 7:06 pm
I cannot speak for Microsoft, but I certainly hope they never drop the original join syntax. The first consideration is that infixed joins won't work when I have a select statement based on one table. Duh!
But more than that, the set based original join syntax can't be deprecated without destroying the language and it works just fine. You will find a lot of SQL products have optimizers that were based on being able to rearrange the FROM clause. Technically, the infixed syntax has to be "effectively" executed in the order written. The first thing that most of the compilers do when they get a bunch of infixed joins they convert them into the "old syntax"
Conceptually, the original syntax was based on sets, and not a sequence of operation. If you had a college course on series, sequences, and summations, you will have run into the "big Sigma" notation. this is what the original join notation was based on. When you when you were in high, you probably ran into the string of +'s notation (1+ 2+3+.. +n) for set-oriented operation the bad news is it sequential by its nature and incision not really thinking in sets yet. We got to the big Sigma notation Donald Knuth extended the indexing to include logical expressions (summation over i, where i is an element of the set of primes, etc) and worried about empty sets for the index.
I loved being able to rearrange the where clauses in my queries so as to put all of the predicates in various orders, based on the tables to which they applied. I found I could improve readability, spot redundancies, and simplify the logic. With the infix notation for inner joins, this is a horrible mess. The rules are that the on clause is associated with the nearest join pair. But you can spread them all over the place! I've seen people do all of the joins at the start and then follow it with all of the on clauses at the end. Now try to look back at the page and associate which condition with which search condition.
The outer joins do have to be infixed. Chris Date, many decades ago, did a very nice article in one of the trade magazines on why the vendor extended equality (*= or += or OUTER ) did not work. When a proposal based on Chris Date's work came to the committee, we proved the Dave McGovern's statement that "a committee never met a feature it didn't like" was too true. If you look at the full specs on the infixed join operators, you'll find all kinds of things you never thought about (outer union, corresponding, etc.)
Please post DDL and follow ANSI/ISO standards when asking for help.
November 23, 2018 at 8:16 am
Jeff Moden - Wednesday, November 21, 2018 1:03 PMdrew.allen - Wednesday, November 21, 2018 11:33 AMJeff Moden - Wednesday, November 21, 2018 9:06 AMScottPletcher - Monday, November 19, 2018 11:57 AMMS 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
Heh... I'll see your HOGWASH and raise you one BULLSH1T! MS won't make the differentiation. The prof there is that they didn't make the change when they changed the outer joins. 😉
There is a difference between "can't" and "won't". I was saying that it was possible, I agree that it is unlikely.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 23, 2018 at 8:51 am
I have recently been moving a lot of DBs to SQL2017 and doing an intial check with the Data Migration Assistant.
It produces the following warning with the old join syntax:
Starting with database compatibility level 90 and higher, in rare occasions, the 'unqualified join' syntax can cause 'missing join predicate' warnings, leading to long running queries.
An example of "Unqualified join" is
select * from table1, table2
where table1.col1 = table2.col1
Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins:
• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN
• FULL OUTER JOIN or FULL JOIN
• INNER JOIN
November 23, 2018 at 10:58 am
Ken McKelvey - Friday, November 23, 2018 8:51 AMI have recently been moving a lot of DBs to SQL2017 and doing an intial check with the Data Migration Assistant.
It produces the following warning with the old join syntax:Starting with database compatibility level 90 and higher, in rare occasions, the 'unqualified join' syntax can cause 'missing join predicate' warnings, leading to long running queries.
An example of "Unqualified join" is
select * from table1, table2
where table1.col1 = table2.col1Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins:
• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN
• FULL OUTER JOIN or FULL JOIN
• INNER JOIN
If those databases come from a server where the compatibility level is less than that of SQL 2014, then those DBs will undergo a significant change under the newer SQL Server version, as SQL 2014 introduced a new optimizer, and those changes can make a fair number of procedures and other code not run very well, and this warning is probably reflective of this. Code that does a cartesian product as a result of a JOIN with no condition are particularly vulnerable to the change in the optimizer. While they may still work, that's the kind of code you probably don't want to have hanging around anyway. FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 23, 2018 at 12:08 pm
drew.allen - Friday, November 23, 2018 8:16 AMJeff Moden - Wednesday, November 21, 2018 1:03 PMdrew.allen - Wednesday, November 21, 2018 11:33 AMJeff Moden - Wednesday, November 21, 2018 9:06 AMScottPletcher - Monday, November 19, 2018 11:57 AMMS 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
Heh... I'll see your HOGWASH and raise you one BULLSH1T! MS won't make the differentiation. The prof there is that they didn't make the change when they changed the outer joins. 😉
There is a difference between "can't" and "won't". I was saying that it was possible, I agree that it is unlikely.
Drew
Then take it easy on terms like "Hogwash". We're all in this together. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply