Menu
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?
324
SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. Aside from a few minor incompatibilities, the SQL-89 standard is forward-compatible with SQL-92. SQL-92. First published.
"ANSI SQL" is a series of standards for modeling and manipulating data. "SQL" is whatever bits of ANSI SQL a SQL engine vendor chooses to implement, plus whatever else they want to add.
SQL is a popular relational database language first standardized in 1986 by the American National Standards Institute (ANSI). Since then, it has been formally adopted as an International Standard by the International Organization for Standardization (ISO) and the International Electrotechnical Commission (IEC).
The ANSI 89 syntax uses a cross join or comma-delimited list of tables, and places the join in the WHERE clause. The ANSI 92 syntax uses a set of key words like INNER JOIN , LEFT JOIN , et cetera, and puts the join in an ON or USING clause, which are often referred to as subclauses.
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 nonstandard (+) 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.
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
