February 14, 2017 at 2:03 pm
I recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.
I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
February 14, 2017 at 2:11 pm
sjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Can you post the entire set of JOINs that appear in the query? What MS thinks is ANSI might differ from what others think, or, they may have deprecated support but not yet actually removed it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 14, 2017 at 3:54 pm
sjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Do you mean joining in the where clause instead of the on clause?
In that case, I don't think it's not truly non-ANSI (and I hope I get my versions right) as it follows ansi-89 standards and is still supported on most platforms. But I believe the ansi-89 standards are being deprecated and it's recommended to do the joins in the on clause which I think came with ansi-92 standards.
And then I lost track when things moved to ISO standards as it makes my head hurt.
Sue
February 15, 2017 at 10:08 am
Sue_H - Tuesday, February 14, 2017 3:54 PMsjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Do you mean joining in the where clause instead of the on clause?
In that case, I don't think it's not truly non-ANSI (and I hope I get my versions right) as it follows ansi-89 standards and is still supported on most platforms. But I believe the ansi-89 standards are being deprecated and it's recommended to do the joins in the on clause which I think came with ansi-92 standards.
And then I lost track when things moved to ISO standards as it makes my head hurt.Sue
If this is what you are seeing:
select *
from dbo.table1 t1, dbo.table2 t2
where t1.col1 = t2.col2
This is ANSI-89 style inner join and is still supported, in fact, I doubt this could be stopped unless you deprecate using just a list of tables in the FROM clause and I don't see that happening.
February 15, 2017 at 9:22 pm
Lynn Pettis - Wednesday, February 15, 2017 10:08 AMSue_H - Tuesday, February 14, 2017 3:54 PMsjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Do you mean joining in the where clause instead of the on clause?
In that case, I don't think it's not truly non-ANSI (and I hope I get my versions right) as it follows ansi-89 standards and is still supported on most platforms. But I believe the ansi-89 standards are being deprecated and it's recommended to do the joins in the on clause which I think came with ansi-92 standards.
And then I lost track when things moved to ISO standards as it makes my head hurt.Sue
If this is what you are seeing:
select *
from dbo.table1 t1, dbo.table2 t2
where t1.col1 = t2.col2This is ANSI-89 style inner join and is still supported, in fact, I doubt this could be stopped unless you deprecate using just a list of tables in the FROM clause and I don't see that happening.
I agree. Deprecating old equi-joins would make correlated sub-queries like those in WHERE EXISTS break. If they ever did such a thing, there would be a march on Redmond and I'd be one of the ones at the front of the line. It's bad enough that they're deprecated things like being able to NOT use semi-colons. When that eventually happens, it's going to break a world of legacy code that currently still works just fine.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2017 at 7:56 am
Jeff Moden - Wednesday, February 15, 2017 9:22 PMLynn Pettis - Wednesday, February 15, 2017 10:08 AMSue_H - Tuesday, February 14, 2017 3:54 PMsjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Do you mean joining in the where clause instead of the on clause?
In that case, I don't think it's not truly non-ANSI (and I hope I get my versions right) as it follows ansi-89 standards and is still supported on most platforms. But I believe the ansi-89 standards are being deprecated and it's recommended to do the joins in the on clause which I think came with ansi-92 standards.
And then I lost track when things moved to ISO standards as it makes my head hurt.Sue
If this is what you are seeing:
select *
from dbo.table1 t1, dbo.table2 t2
where t1.col1 = t2.col2This is ANSI-89 style inner join and is still supported, in fact, I doubt this could be stopped unless you deprecate using just a list of tables in the FROM clause and I don't see that happening.
I agree. Deprecating old equi-joins would make correlated sub-queries like those in WHERE EXISTS break. If they ever did such a thing, there would be a march on Redmond and I'd be one of the ones at the front of the line. It's bad enough that they're deprecated things like being able to NOT use semi-colons. When that eventually happens, it's going to break a world of legacy code that currently still works just fine.
I agree in spirit with the deprecation of NOT using semicolons and that if they ever enforce that deprecation a lot of code will break include their own code. With that said, that may be one deprecation item that may never happen. On the other side of that, I am a strong proponent of using semicolons as terminators, not begininators.
February 16, 2017 at 9:11 am
Thank you all for responding.
Lynn - Yes, the query you posted is what I am referring to. I didn't realized that ANSI-89 queries with inner joins were still executable.
select *
from dbo.table1 t1, dbo.table2 t2
where t1.col1 = t2.col2
Thanks again!
February 16, 2017 at 10:08 am
Yes, inner joins like that are still valid because they are still unambiguous and thus 100% logically valid. Equi-joins function exactly the same using WHERE joins rather than a JOIN clause. Old-style outer joins can have ambiguities, which is why the were unequivocally removed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 16, 2017 at 11:16 am
Jeff Moden - Wednesday, February 15, 2017 9:22 PMLynn Pettis - Wednesday, February 15, 2017 10:08 AMSue_H - Tuesday, February 14, 2017 3:54 PMsjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Do you mean joining in the where clause instead of the on clause?
In that case, I don't think it's not truly non-ANSI (and I hope I get my versions right) as it follows ansi-89 standards and is still supported on most platforms. But I believe the ansi-89 standards are being deprecated and it's recommended to do the joins in the on clause which I think came with ansi-92 standards.
And then I lost track when things moved to ISO standards as it makes my head hurt.Sue
If this is what you are seeing:
select *
from dbo.table1 t1, dbo.table2 t2
where t1.col1 = t2.col2This is ANSI-89 style inner join and is still supported, in fact, I doubt this could be stopped unless you deprecate using just a list of tables in the FROM clause and I don't see that happening.
I agree. Deprecating old equi-joins would make correlated sub-queries like those in WHERE EXISTS break. If they ever did such a thing, there would be a march on Redmond and I'd be one of the ones at the front of the line. It's bad enough that they're deprecated things like being able to NOT use semi-colons. When that eventually happens, it's going to break a world of legacy code that currently still works just fine.
I disagree. The syntax of a correlated subquery is not determined by the syntax for an equi-join. Deprecating one will have no effect on the other.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 16, 2017 at 4:19 pm
drew.allen - Thursday, February 16, 2017 11:16 AMJeff Moden - Wednesday, February 15, 2017 9:22 PMLynn Pettis - Wednesday, February 15, 2017 10:08 AMSue_H - Tuesday, February 14, 2017 3:54 PMsjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Do you mean joining in the where clause instead of the on clause?
In that case, I don't think it's not truly non-ANSI (and I hope I get my versions right) as it follows ansi-89 standards and is still supported on most platforms. But I believe the ansi-89 standards are being deprecated and it's recommended to do the joins in the on clause which I think came with ansi-92 standards.
And then I lost track when things moved to ISO standards as it makes my head hurt.Sue
If this is what you are seeing:
select *
from dbo.table1 t1, dbo.table2 t2
where t1.col1 = t2.col2This is ANSI-89 style inner join and is still supported, in fact, I doubt this could be stopped unless you deprecate using just a list of tables in the FROM clause and I don't see that happening.
I agree. Deprecating old equi-joins would make correlated sub-queries like those in WHERE EXISTS break. If they ever did such a thing, there would be a march on Redmond and I'd be one of the ones at the front of the line. It's bad enough that they're deprecated things like being able to NOT use semi-colons. When that eventually happens, it's going to break a world of legacy code that currently still works just fine.
I disagree. The syntax of a correlated subquery is not determined by the syntax for an equi-join. Deprecating one will have no effect on the other.
Drew
So what do you think the syntax for a correlated sub-query actually is?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2017 at 10:36 am
sjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Both the both the traditional set oriented and the newer in fixed join notation's are legal syntax. The story of how we approved the in fixed notations is really pretty interesting. I probably write an article on it. But what I found is that people who use the traditional notation think in sets, while those who use the infix notation are stuck with a procedural linear mindset. Is the difference between writing with uppercase Sigma summation notation and a string chain of +'s.
The history of the outer join is also interesting because of are so many flavors of it 😎
Please post DDL and follow ANSI/ISO standards when asking for help.
February 17, 2017 at 10:46 am
jcelko212 32090 - Friday, February 17, 2017 10:36 AMsjs-36273 - Tuesday, February 14, 2017 2:03 PMI recently found a non-ANSI select statement with equal joins executing against a SQL 2012 (compatibility mode SQL Server 2012(110)) server that I support. I know non-ANSI left and right outer joins are not supported in SQL 2012 but was surprised to see that SQL 2012 would execute a query with non-ANSI equal joins. I was under the impression that all non-ANSI join queries would not execute in SQL 2012.I was able to get the vendor to change the query to be ANSI compliant and the performance of the query improved.
Why are non-ANSI equal join queries still allowed to execute in SQL 2012? Thanks
Both the both the traditional set oriented and the newer in fixed join notation's are legal syntax. The story of how we approved the in fixed notations is really pretty interesting. I probably write an article on it. But what I found is that people who use the traditional notation think in sets, while those who use the infix notation are stuck with a procedural linear mindset. Is the difference between writing with uppercase Sigma summation notation and a string chain of +'s.
The history of the outer join is also interesting because of are so many flavors of it 😎
I prefer the ANSI-92 joins as it allows me to concentrate first on getting the dataset then to filter it down to what is needed. Keeping them separate helps when someone else has to look at and maybe modify the code. Also, when requirements change and additional data needs to be added from other tables makes bringing them in easier.
February 21, 2017 at 10:42 am
Jeff Moden - Thursday, February 16, 2017 4:19 PMdrew.allen - Thursday, February 16, 2017 11:16 AMI disagree. The syntax of a correlated subquery is not determined by the syntax for an equi-join. Deprecating one will have no effect on the other.Drew
So what do you think the syntax for a correlated sub-query actually is?
I think that the syntax for a correlated subquery is more like the syntax for a CROSS JOIN than an EQUI-JOIN. NOTE: This is only in reference to the syntax. It has no bearing on how that syntax is actually implemented.
There are also other differences in correlated subqueries and joins, specifically, correlated subqueries can directly manipulate the data that is returned whereas joins cannot. For example, returning aggregates instead of details.
Drew
Sorry, I was out of town for the long weekend.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply