Is there a specific and accepted standard SQL language?

Question

I remember how in college I took a database class, where we, the students, were given the opportunity to choose our database management system. As my first experience with databases, I remember that I was confused by the fact that the SQL expressions had different syntax, depending on the database management system used. Prior to this class, I had the impression that the SQL language is a universal language that the user can use to communicate with all databases.

So what is the deal? Is there a specific SQL standard? If so, why is the production of a database management system different from the specification? Was the standard incomplete with the idea that companies would use it as the basis for their language extensions? Was the original SQL standard obsolete and manufacturers needed to increase functionality? Is / was the original not updated at a pace to stay up to date with the current needs of the application? Create a business model for income?

Thanks.

Answer 1

Yes, there are ANSI SQL standards; see, for example, the SQL-92 project . However, each database provider implements an "extended subset" of standard SQL - and if you think about it, something is an "extended subset" of something else, in a way ;-). Sure, there are commercial reasons for this, but even non-commercial implementations like PostgreSQL behave the same way ...

Answer 2

There are SQL standards:

http://en.wikipedia.org/wiki/SQL#Standardization

However, most DBMSs still use a somewhat standard or extended version of the standard. (Microsoft SQL Server is the prime suspect).

Answer 3

You can distinguish between a data manipulation language (SELECT, UPDATE, INSERT: SQL), a data definition language (CREATE, DELETE: SQL), a data management language (GRANT: SQL).

SQL can cover all of these parts, but sometimes vendors do not like how SQL does it. Sometimes they do not implement the details (see DCL). More often than not, the standard “footprint” is after reality (see Xpath expressions in DML), and they simply create their own syntax.

Answer 4

Was the original SQL standard obsolete and manufacturers needed to build more functionality? Is / was original not updated at a pace to stay up to date with modern applications? Was it to create a business model for generating income?

I think these are the main factors. The standard did not cover some useful functions, and suppliers sought to add functions that would force customers to choose their implementation, rather than lobbying to get standardization as quickly as possible.

Answer 5

There is standardized SQL. According to other answers, database providers come with extensions specific to their database engines.

From my experience with databases (especially in the case of large databases), trying to create a cross-provider database makes little sense. For example, extensions in the data definition language allow the / dba developer to optimize database performance using provider-specific functions (for example, physical presentation of tables, information about table storage, indexing, etc.). In the case of queries, again, for large databases, you may need to specify a database engine to build an execution plan. The hint syntax, again, is vendor specific.

Here are some examples of "non-standard" functions that can have a significant impact.

Again, in my experience, unfortunately, many clients or technical guys (architects?) Pay less attention to the details of the database and miss the powerful functions provided by the database for data management and access.