Why isn't SQL ANSI-92 standard better adopted

Question

At every company I have worked at, I have found that people are still writing their SQL queries in the ANSI-89 standard:

select a.id, b.id, b.address_1
from person a, address b
where a.id = b.id

rather than the ANSI-92 standard:

select a.id, b.id, b.address_1
from person a
inner join address b
on a.id = b.id

For an extremely simple query like this, there's not a big difference in readability, but for large queries I find that having my join criteria grouped in with listing out the table makes it much easier to see where I might have issues in my join, and let's me keep all my filtering in my WHERE clause. Not to mention that I feel that outer joins are much intuitive than the (+) syntax in Oracle.

As I try to evangelize ANSI-92 to people, are there any concrete performance benefits in using ANSI-92 over ANSI-89? I would try it on my own, but the Oracle setups we have here don't allow us to use EXPLAIN PLAN - wouldn't want people to try to optimize their code, would ya?

Answer 1

According to "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, of the six or eight RDBMS brands they tested, there was no difference in optimization or performance of SQL-89 versus SQL-92 style joins. One can assume that most RDBMS engines transform the syntax into an internal representation before optimizing or executing the query, so the human-readable syntax makes no difference.

I also try to evangelize the SQL-92 syntax. Sixteen years after it was approved, it's about time people start using it! And all brands of SQL database now support it, so there's no reason to continue to use the abhorrent (+) Oracle syntax or *= Microsoft/Sybase syntax.

As for why it's so hard to break the developer community of the SQL-89 habit, I can only assume that there's a large "base of the pyramid" of programmers who code by copy & paste, using ancient examples from books, magazine articles, or another code base, and these people don't learn new syntax abstractly. Some people pattern-match, and some people learn by rote.

I am gradually seeing people using SQL-92 syntax more frequently than I used to, though. I've been answering SQL questions online since 1994.

Answer 2

I can answer from the point of view of an average developer, knowing just enough SQL to understand both syntaxes, but still googling the exact syntax of insert each time I need it... :-P (I don't do SQL all day, just fixing some problems from time to time.)

Well, actually, I find the first form more intuitive, making no apparent hierarchy between the two tables. The fact I learned SQL with possibly old books, showing the first form, probably doesn't help... ;-)
And the first reference I find on a sql select search in Google (which returns mostly French answers for me...) first shows the older form (then explain the second one).

Just giving some hints on the "why" question... ^_^ I should read a good, modern book (DB agnostic) on the topic. If somebody has suggestions...

Answer 3

First let me say that in SQL Server the outer join syntax (*=) does not give correct results all the time. There are times when it interprets that as a cross join and not an outer join. So right there is a good reason to stop using it. And that outer join syntax is a deprecated feature and will not be in the next version of SQL Server after SQL Server 2008. You'll still be able to do the inner joins but why on earth would anyone want to? They are unclear and much much harder to maintain. You don't easily know what is part of the join and what is really just the where clause.

One reason why I believe you should not use the old syntax is that understanding joins and what they do and do not do is a critical step for anyone who will write SQL code. You should not write any SQL code without understanding joins thoroughly. If you understand them well, you will probably come to the conclusion that the ANSI-92 syntax is clearer and easier to maintain. I've never met a SQL expert who didn't use the ANSI-92 syntax in preference to the old syntax.

Most people who I have met or dealt with who use the old code, truly don't understand joins and thus get into trouble when querying the database. This is my personal experience so I'm not saying it is always true. But as a data specialist, I've had to fix too much of this junk through the years not to believe it.

Answer 4

Here are a few points comparing SQL-89, and SQL-92 and clearing up some misconceptions in other answers.

1. NATURAL JOINS are a horrible idea. They're implicit and they require meta-information about the table. Nothing about SQL-92 requires their use so simply ignore them. They're not relevant to this discussion.
2. USING is a great idea, it has two effects:
   1. It produces only one column on the result set from an equijoin.
   2. It's enforces a sound and sane convention. In SQL-89 you had people writing the column id on both tables. After you join the tables, this becomes and ambiguous and it requires explicit aliasing. Further, the ids on the join almost certainly had different data. If you join person to company, you now have to alias one id to person_id, and one id to company_id, without which the join would produce two ambiguous columns. Using a globally-unique identifier for the table's surrogate key is the convention the standard rewards with USING.
3. The SQL-89 syntax is an implicit CROSS JOIN. A CROSS JOIN doesn't reduce the set, it implicitly grows it. FROM T1,T2 is the same as FROM T1 CROSS JOIN T2, that produces a Cartesian join which is usually not what you want. Having the selectivity to reduce that removed to a distant WHERE conditional means that you're more likely to make mistakes during design.
4. SQL-89 , and SQL-92 explicit JOINs have different precedence. JOIN has a higher precedence. Even worse, some databases like MySQL got this wrong for a very long time.. So mixing the two styles is a bad idea, and the far more popular style today is the SQL-92 style.