Why does the SQL language vary from DBMS to DBMS?


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 on the network to connect to the database.

So I opted for PostgreSQL because it was open source , but I had never used it before > So when I created my first table I noticed something different, the "schemas" and instead of AUTO_INCREMENT PostgreSQL uses SERIAL .

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

asked by anonymous 29.08.2018 / 20:37

1 answer


The exact reason is difficult to define, so it has become so. Probably because everyone felt that it best served their needs. This is partly a technical and commercial ( lock-in) issue.

The ANSI or ISO SQL language is one, but strictly follows who it wants, and no one wanted. In some ways the correct thing is to call each SQL with a specific name and some call itself, but people have decided to pretend they do not and use the pure SQL name for everything.

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

SQL is a committee-defined language, and somewhat out of the reality of the market, so it only exists standardized on paper. Even because everything started wrong. The language is too complex to be followed and is ambiguous or omitted in certain things.

There is time that 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 still an old one (it's common to adopt SQL -92 - when they started to worry about it and started to make sense - some selected things more modern, but even those more basic have exceptions than do not conform). That's kind of mean. Forget the fallacy that SQL is standardized, it should be, in practice it is not. Note that even the default has the full name of the specification year.

SQL-99 already has things that are complicated to implement or not unanimous of its usefulness. SQL-2003 has gone to have resources that soon started to seem like a bad idea, or started taking things that were previously bad defined, which is already breaking the credibility of the committee. And they had to sort things out in SQL-2006 . Then came the SQL-2008 with some news. And then the SQL-2011 with temporal functions. We are now in SQL-2016 fixing previous errors.

Part of the problem could be solved, but part not, because the language can not be used the same in the product, especially in old product that started in a way and the pattern set different after, the committee always runs behind and when the standard is published who implemented different can not change more by the base running.

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 it wants from result and the implementation of where it will run influences its way of working. And it worsens the fact that it does not have a certifier, who says it meets the standard is the vendor.

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

PostgreSQL is considered quite close to the default. SQLite is also what it implements. In general, vendors change the syntax and semantics of what they do. Some accept the standard syntax but have empty or altered semantics, which is much worse.

29.08.2018 / 21:15