Why does the SQL language vary from DBMS to DBMS?

Question

I was used to using SQL for Web projects, always using Mysql.

One day I had to write a C++ program that used databases. At first I chose Sqlite but needed other computers in the network to connect to the database.

So I opted for Postgresql to be open source, but had never used before> Soon when I went to create my first table I noticed something different, the "schemas" and in place of the AUTO_INCREMENT Postgresql utilizes SERIAL.

If the language is standardized then why does it vary in each of the systems?

Answer 1

The exact reason is difficult to define, is because it became so. Probably because each one thought that it better met their needs. It is a technical part and a commercial part (lock-in).

The ANSI or ISO SQL language is one, but strictly follows who wants, and nobody wanted. In a way the correct is to call each SQL with a specific name and some even call, but people decided to pretend that no and use the pure SQL name for everything.

But it is a mistake for people to use the name in the wrong way, and that gives the wrong impression. When someone asks here could tell which SQL you are talking about. So understand that you do not have an SQL, you have several.

SQL is a language defined by committee, and a little outside the reality of the market, so it only exists standardized on paper. Even because it started all wrong. The language is too complex to be followed and is ambiguous or missing in certain things.

There is a time when the industry should rethink and create something better at least as an option to use. The same goes for HMTL/CSS and other wrong things.

By marketing Sgdbs say they adopt the standard, but only partially, and even then an old one (it is common to adopt the SQL-92 - when they started worrying about it and it started to make sense - some more modern selected things, but even those more basic ones have exceptions than they don’t conform to). That’s kind of dirty. Forget the fallacy that SQL is standardized, it should be, in practice it is not. Note that even the standard has as its full name the year of the specification.

The SQL-99 has already got things that are complicated to implement or not unanimous of their usefulness. The SQL-2003 Soon afterwards, they started to think it was a bad idea, or they started to take things that were previously poorly defined, which is already breaking the committee’s credibility. And they had to pack a few things in the SQL-2006. Then came the SQL-2008 with some news. And then the SQL-2011 with temporal functions. Now we are in the SQL-2016 fixing previous mistakes.

Part of the problem could be solved, but part didn’t, because the language can’t be used the same in the product, mainly in an old product that started in a way and the pattern established differently afterwards, the committee always runs behind and when the standard is published who implemented differently can no longer change by running base.

It is different from programming language that is made to run on a computer to have it run something. SQL is declarative to indicate what you want the result and the implementation from which you run influences how it works. And it worsens the fact that there is no certifier, who says that meets the standard is the supplier.

Mysql is a huge offender of the standard, never called to be different.

Postgresql is considered very close to the standard. Sqlite is also in what it implements. In general suppliers change the syntax and semantics of what they do. Some accept standard syntax, but have empty or altered semantics, which is much worse.