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

Inertia and practicality.

ANSI-92 SQL is like touch-typing. In some theoretical way it might make everything better someday, but I can type much faster looking at the keys with four fingers now. I would need to go backwards in order to go forwards, with no guarantee that there would ever be a pay-off.

Writing SQL is about 10% of my job. If I need ANSI-92 SQL to solve a problem that ANSI-89 SQL can't solve then I'll use it. (I use it in Access, in fact.) If using it all the time would help me solve my existing problems much faster, I'd spend the time to assimilate it. But I can whip out ANSI-89 SQL without ever thinking about the syntax. I get paid to solve problems--thinking about SQL syntax is a waste of my time and of my employer's money.

Someday, young Grasshopper, you'll be defending your use of ANSI-92 SQL syntax against young people whining that you should be using SQL3 (or whatever). And then you'll understand. :-)

Answer 2

A new SQL standard inherits everything from the previous standard, a.k.a. 'the shackles of compatibility'. So the 'old' / 'comma-separated' / 'unqualified' join style is perfectly valid SQL-92 sytax.

Now, I argue that SQL-92's NATURAL JOIN is the only join you need. For example, I argue it is superior to inner join because it does not generate duplicate columns - no more range variables in SELECT clauses to disambiguate columns! But I can't expected to change every heart and mind, so I need to work with coders who will continue to adopt what I personally consider to be legacy join styles (and they may even refer to range variables as 'aliases'!). This is the nature of teamwork and not operating in a vacuum.

One of the criticisms of the SQL language is that the same result can be obtained using a number of semantically-equivalent syntaxes (some using relational algebra, some using the relational calculus), where choosing the 'best' one simply comes down to personal style. So I'm as comfortable with the 'old-style' joins as I am with INNER. Whether I'd take the time to rewrite them as NATURAL depends on context.

Answer 3

Reasons people use ANSI-89 from my practical experience with old and young programmers and trainees and fresh graduates:

• They learn SQL from existing code they see (rather than books) and learn ANSI-89 from code
• ANSI-89 because is less typing
• They do not think about it and use one or other style and do not even know which of both is considered new or old and do not care either
• The idea that code is also a communication to the next programmer coming along maintaining the code does not exist. They think they talk to the computer and the computer does not care.
• The art of "clean coding" is unknown
• Knowledge of programming language and SQL specifically is so poor that they copy and paste together what they find elsewhere
• Personal preference

I personally prefer ANSI-92 and change every query I see in ANSI-89 syntax sometimes only to better understand the SQL Statement at hand. But I realized that the majority of people I work with are not skilled enough to write joins over many tables. They code as good as they can and use what they memorized the first time they encountered a SQL statement.

Answer 4

I was taught ANSI-89 in school and worked in industry for a few years. Then I left the fabulous world of DBMS for 8 years. But then I came back and this new ANSI 92 stuff was being taught. I have learned the Join On syntax and now I actually teach SQL and I recommend the new JOIN ON syntax.

But the downside that I see is correlated subqueries don't seem to make sense in the light of ANSI 92 joins. When join information was included in the WHERE and correlated subqueries are "joined" in the WHERE all seemed right and consistent. In ANSI 92 table join criteria is not in the WHERE and subquery "join" is, the syntax seems inconsistent. On the other hand, trying to "fix" this inconsistency would probably just make it worse.

Answer 5

Oracle does not implement ANSI-92 at all well. I've had several problems, not least because the data tables in Oracle Apps are so very well endowed with columns. If the number of columns in your joins exceeds about 1050 columns (which is very easy to do in Apps), then you will get this spurious error which makes absolutely no logical sense:

ORA-01445: cannot select ROWID from a join view without a key-preserved table.

Re-writing the query to use old style join syntax makes the issue disappear, which seems to point the finger of blame squarely at the implementation of ANSI-92 joins.

Until I encountered this problem, I was a steadfast promoter of ASNI-92, because of the benefits in reducing the chance of an accidental cross join, which is far too easy to do with old-style syntax.

Now, however, I find it much more difficult to insist on it. They point to Oracle's bad implementation and say "We'll do it our way, thanks."

Answer 6

A few reasons come to mind:

• people do it out of habit
• people are lazy and prefer the "old style" joins because they involve less typing
• beginners often have their problems wrapping their heads around the SQL-92 join syntax
• people don't switch to new syntax just because it is there
• people are unaware of the benefits the new (if you want to call it that) syntax has, primarily that it enables you to filter a table before you do an outer join, and not after it when all you have is the WHERE clause.

For my part, I do all my joins in the SQL-92 syntax, and I convert code where I can. It's the cleaner, more readable and powerful way to do it. But it's hard to convince someone to use the new style, when they think it hurts them in terms of more typing work while not changing the query result.