Is there a 'standard' SQL that can replace all the various custom versions?

Question

I have been writing SQL for over 10 years now. I am extremely proficient at it and have experience working in SQL Server, Oracle, MySQL, PostgreSQL, etc. While there are multiple standards out there, they seem to be more suggestions than standards. When you start talking about column types and stored procedures, there's almost no consistency across the board.

I wonder if someone ever considered defining a new querying language. I recently played around with some ideas. Here are some examples: https://gist.github.com/jehugaleahsa/03888d13ef2745cb67d0. Of course, this just covers the most obvious DML. This syntax would make it easier to support auto-completion, give more control over the temp table generation, make the join syntax easier, make working with grouped data easier and make sub-expressions/calculations easier to build up, just to name a few. I especially like it because it could very easily be extended to support some noSQL databases, as well. Imagine what a committee could come up with given more time!

My question is whether any effort has been put into defining a different querying language, supported by the different providers. I recognize that SQL is "good enough" and that defining and implementing a new standard would be a monumental undertaking. I know there were a lot of SQL standards proposed over the years. I just wonder if a non-"SQL" standard ever was proposed and whether it made progress or just fizzled out.

Answer 1

The answer to this was ANSI SQL.

Although initial adoption was hard, especially for databases like Oracle, many of them now allow the ANSI standard.

For example Oracle started allowing that format in 9i (see http://allthingsoracle.com/ansi-sql/)

Also - PostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the ANSI-SQL:2008 standard (http://www.postgresql.org/about/)

For mysql, see http://dev.mysql.com/doc/refman/5.0/en/compatibility.html which states:

This section describes how MySQL relates to the ANSI/ISO SQL standards. MySQL Server has many extensions to the SQL standard, and here you can find out what they are and how to use them. You can also find information about functionality missing from MySQL Server, and how to work around some of the differences.

The SQL standard has been evolving since 1986 and several versions exist. In this manual, “SQL-92” refers to the standard released in 1992, “SQL:1999” refers to the standard released in 1999, “SQL:2003” refers to the standard released in 2003, and “SQL:2008” refers to the most recent version of the standard, released in 2008.

Answer 2

... make the join syntax easier, make working with grouped data easier and make sub-expressions/calculations easier to build up, just to name a few. I especially like it because it could very easily be extended to support some noSQL databases...

Now I think that's where One Standard To Rule Them All breaks down... Current NoSQL solutions are not as optimal as traditional RDBMS for running join operations, if they even support it natively in the first place, and for good reasons. Sure, they are trying to improve in this aspect, but there's no magic bullet yet. And why stop at NoSQL vs RDBMS, how about graph databases? Cypher for Neo4j has a MATCH clause instead that 'is analogous to the JOIN in SQL'.

I think one should look towards polyglot persistence frameworks to adequately handle the "various custom SQL" problem. If it turns out that your application uses a small in-memory RDBMS solution for simple caching, and writes to a multi-terabyte data warehouse powered by a NoSQL solution, the "solution" to a standardized database interface may not be some mytical common dialect, but a well-written framework that understands the differences and handles appropriately.

Answer 3

No. The reality is that the SQL required by Oracle, DB2, SQLite, MySQL, PostgreSQL, and name your favorite other relational DBMS is different from the SQL required by each of the other DBMS systems--the various SQL "standards" notwithstanding.

Very basic functionality and semantics, like what data types are supported (and what they're called), how row identifiers are allocated (with what semantics), and what type of operations are supported (including keywords, functions, restrictions, and semantics) differ enormously. These many differences are why DBMS migrations are so famously difficult, and why they require custom tools and consulting services.

Yes the basic form of SELECT * FROM table WHERE ... is common across all relational database systems. But the devil of real applications is in the details, and the details differ in important ways between different data stores.

This is to say nothing of the even greater differences seen between relational and non-relational DBMSs (be they traditional hierarchical, network, flat file, record stores, or the new hotness of NoSQL). Even if they have "SQL compatibility layers," the extent and semantics of their SQL vary greatly.

SQL (and other data definition and query langauge) homogenization is an approach that has been worked on, tried, and hoped for at least four decades now. It never quite works out as well as hoped. Given the fundamental economic incentive for DBMS producers to prevent their customers from easily migrating to competing products or vendors, there's little hope of this being resolved any time in the next decade or two.