Non-ANSI equal join - SQL 2012

  • sjs-36273

    SSCommitted

    Points: 1854

    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

  • sgmunson

    SSC Guru

    Points: 110504

    sjs-36273 - Tuesday, February 14, 2017 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

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Sue_H

    SSC Guru

    Points: 90700

    sjs-36273 - Tuesday, February 14, 2017 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

    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

  • Lynn Pettis

    SSC Guru

    Points: 442342

    Sue_H - Tuesday, February 14, 2017 3:54 PM

    sjs-36273 - Tuesday, February 14, 2017 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

    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.

  • Jeff Moden

    SSC Guru

    Points: 996829

    Lynn Pettis - Wednesday, February 15, 2017 10:08 AM

    Sue_H - Tuesday, February 14, 2017 3:54 PM

    sjs-36273 - Tuesday, February 14, 2017 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

    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.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442342

    Jeff Moden - Wednesday, February 15, 2017 9:22 PM

    Lynn Pettis - Wednesday, February 15, 2017 10:08 AM

    Sue_H - Tuesday, February 14, 2017 3:54 PM

    sjs-36273 - Tuesday, February 14, 2017 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

    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.

    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.

  • sjs-36273

    SSCommitted

    Points: 1854

    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!

  • ScottPletcher

    SSC Guru

    Points: 98483

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • drew.allen

    SSC Guru

    Points: 76739

    Jeff Moden - Wednesday, February 15, 2017 9:22 PM

    Lynn Pettis - Wednesday, February 15, 2017 10:08 AM

    Sue_H - Tuesday, February 14, 2017 3:54 PM

    sjs-36273 - Tuesday, February 14, 2017 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

    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.

    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

  • Jeff Moden

    SSC Guru

    Points: 996829

    drew.allen - Thursday, February 16, 2017 11:16 AM

    Jeff Moden - Wednesday, February 15, 2017 9:22 PM

    Lynn Pettis - Wednesday, February 15, 2017 10:08 AM

    Sue_H - Tuesday, February 14, 2017 3:54 PM

    sjs-36273 - Tuesday, February 14, 2017 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

    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.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 9021

    sjs-36273 - Tuesday, February 14, 2017 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

    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. 

  • Lynn Pettis

    SSC Guru

    Points: 442342

    jcelko212 32090 - Friday, February 17, 2017 10:36 AM

    sjs-36273 - Tuesday, February 14, 2017 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

    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.

  • drew.allen

    SSC Guru

    Points: 76739

    Jeff Moden - Thursday, February 16, 2017 4:19 PM

    drew.allen - Thursday, February 16, 2017 11:16 AM

    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?

    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 13 (of 13 total)

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