iso sql标准发展及各个版本定义的特性

ISO SQL标准经历了SQL:2016, SQL:2011, SQL:2008, SQL:2006, SQL:2003（这个版本开始，根据主题拆分）, SQL:1999, and SQL-92。

　　从上可知，SQL 2003是现代SQL的分水岭之一。

当前组成部分如下：

• ISO/IEC 9075-1 Framework (SQL/Framework)

• ISO/IEC 9075-2 Foundation (SQL/Foundation)

• ISO/IEC 9075-3 Call Level Interface (SQL/CLI)

• ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)

• ISO/IEC 9075-9 Management of External Data (SQL/MED)

• ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)

• ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)

• ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)

• ISO/IEC 9075-14 XML-related specifications (SQL/XML)

• ISO/IEC 9075-15 Multi-dimensional arrays (SQL/MDA)

ISO/IEC 9075-14:2003 Information technology — Database languages — SQL — Part 14: XML-Related Specifications (SQL/XML)

ISO SQL Standard 2017 as Part 6 – SQL support for JavaScript Object Notation (JSON): ISO / IEC TR 19075-6: 2017 https://www.iso.org/standard/67367.html，https://www.buckenhofer.com/2019/01/json-and-iso-sql-standard/

https://www.postgresql.org/docs/current/features.html  PG ISO/IEC SQL的实现情况，主要到SQL 2016。PostgreSQL内核覆盖了第1, 2, 9, 11, 和14部分，第3部分通过ODBC 驱动实现,第13部分通过PL/Java插件实现。第4, 10, 和15 部分当前无实现。

https://docs.oracle.com/database/121/SQLRF/ap_standard_sql.htm#SQLRF019

分析函数是ISO/IEC 2008的一部分

SQL 89只有平铺的from a,b,c

SQL 92包含了大量的增强，包括定义了外连接语法及集合，如下：

The next revision of the standard was SQL-92 – and it was a major revision. The language introduced by SQL-92 is sometimes referred to as SQL 2. The standard document grew from 120 to 579 pages. However, much of the growth was due to more precise specifications of existing features.

The most important new features were:

• An explicit JOIN syntax and the introduction of outer joins: LEFT JOIN, RIGHT JOIN, FULL JOIN.
• The introduction of NATURAL JOIN and CROSS JOIN   在语义分析时，natural join会被转换为USING。ON也一样，所以优先USING而非ON/NATURAL JOIN
• The introduction of set operations (set union, set intersection, and set difference).
• The introduction of the conditional expression CASE WHEN.
• New scalar operations: string concatenation, substring extraction, and date and time mathematics.
• The CAST operator, which allows the explicit casting of values into types.

SQL-92 also introduced new data definition statements: ALTER and DROP for tables and views, and new data types (date, time, timestamp, interval, bit string, varchar string, and national character strings). This version added an information schema (the standard way to get database metadata, such as table names, table columns, column types, and table constraints). It also introduced temporary tables, transaction isolation levels, and the dynamic execution of queries (dynamic SQL).

ISO/IEC 9075 - SQL Standard

SQL-86 (or SQL-87)

SQL-87 is the ISO 9075:1987 standard of 1987

 

Some defined features of SQL-87 are

• transactions
• create
• read
• update
• delete

 

This standard is available as FIPS 127 / ANSI X3.135-1986 on archive.org (the link was posted on this dba.stackexchange.com answer).

 

This standard was based on System R.

SQL-89

SQL-89 is the ISO/IEC 9075:1989 standard of 1989

 

SQL-89 added foreign and primary key constraints.

 

This standard had about 120 pages.

SQL-92

SQL-92 is the ISO/IEC 9075:1992 standard of 1992.

 

SQL-92 added

• subqueries in place of named tables.
• Internationalization.
• Embedded SQL in programming languages

 

SQL-92 mentions the four isolation levels (Read uncomitted, read comitted, repeatable read and serializable).

 

This standard had 628 pages.

 

In MS access, ANSI 92 syntax can be enabled under the menu File -> Options -> Object Designers -> Query Design -> SQL Server Compatible Syntax

SQL:1999

SQL:1999 is the ISO/IEC 9075:1999 standard of 1999.

 

SQL:1999 introduced the first of two collection types: the array. (These types apparently are also referred to as User Defined Types).

 

An amendment to SQL:1999 in 2000 added window functions. These were then incorporated into the SQL:2003 standard.

 

The full standard had about 2200 pages.

SQL:2003

SQL:2003 is the ISO/IEC 9075:2003 standard of 2003

 

SQL:2003 introduced the merge statement and the second of two collection types: the multiset.

Data types

Because of lack of support in existing database products, the bit and bit varying data types were removed from the standard. (However, SQL Server still has the bit datatype).

 

On the other hand, three new data types were added:

• bigint
• multiset (which is a collection type).
• XML

Columns

SQL 2003 also introduced

• identity columns, and
• generated columns

SQL:2006

SQL:2006 is the ISO/IEC 9075:2006 standard of 2006

SQL:2008

SQL:2008 is the ISO/IEC 9075:2008 standard of 2008

 

Many corrections.

 

SQL:2008 enhanced the merge statement.

 

SQL:2008 specifies 164 mandatory and 280 optional features.

SQL:2011

SQL:2011 is the ISO/IEC 9075:2011 standard of 2011

 

SQL:2011 added 34 new features to the SQL/Foundation part (all of which are optional). This brought the total of features in SQL/Foundation to 314.

This standard came with 4063 pages.

 

The possibly most important feature of SQL:2011 is the support for temporal databases. Other important features that come with SQL:2011 are

• delete in the merge statement.
• Pipelined DML
• Enhanced call statement (named arguments and default values for arguments)
• Ability to limit fetches (See Oracle SQL: select first n rows / rows between n and m (top n/limit queries))
• Enhanced collection types
• Non-enforced table constraints
• Window enhancement (ntile, navigation within a window (lag, lead, nth_value, first and last), nested navigation within a window, groups option)

 

See Temporal features in SQL:2011 (by Krishna Kulkarni and Jan-Eike Michels covers the most important new functionality that is part of SQL:2011: the ability to create and manipulate temporal tables and Fred Zemke: What's new in SQL:2011.

 

See also: Final Draft (not to be used for reference purposes)

SQL:2016

SQL:2016 is the ISO/IEC 9075:2016 standard of 2016

 

With the exception of part 2, SQL:2016 did not significantly change from the previous version.

 

The most important new features in SQL:2016 are

• Support for JSON data (of the 44 new optional features of SQL:2016, 22 are related to JSON).
• Polymorphic table functions (PTF)
• Row pattern recognition (RPR): apply regular expressions across rows.
• Trigonometric and logarithmic functions

Support for JSON

Unlike SQL/XML (Part 14), JSON was not put in its own part - yet, it is still referred to as SQL/JSON.

 

SQL:2016 does not define a new data type for JSON - it is stored in an SQL string type (for example varchar or clob).

 

See also the technical report ISO/IEC TR 19075-6:2017

Row pattern recognition (RPR)

Row pattern recognition is kind of applying regular expressions across sequences of rows.

 

See also the technical report ISO/IEC TR 19075-5:2016 and features R010 and R020.

Polymorphic table functions (PTF)

Parameters and function return values can be tables whose shape is not known until query time.

 

The feature ID for PTFs is B200.

 

See also the technical report ISO/IEC TR 19075-7:2017

SQL:2019

SQL:2019 added a part 15: Multidimensional arrays (MDA)

SQL:2020

JTC 1/SC 32/WG 3 identified the following areas of interest for the next (as of 2020-02) SQL standard:

• Better support for Big Data applications.
• Graph queries
• Approximate queries/aggregates and uncertain data.
• Integration of statistical packages (for example R).
• Support for map-reduce algorithms
• Streaming/continuous queries
• Support for blockchains
• BASE transactions

Parts of the standard

The SQL standard is published in multiple volumes (aka parts). Currently, these volumes are 2, 3, 4, 9, 10, 11, 13 and 14. (Gaps because some parts were withdrawn in earlier versions of the standards and their numbers are not recycled).

 

The parts are

• 1: Framework
• 2: Foundation
• 3: Call-Level Interface (CLI)
• 4: Persistent Stored Modules (PSM) - for example PL/SQL for Oracle or T-SQL for SQL Server.
• 9: Management of External Data (MED)
• 10: Object Language Bindings (OLB)
• 11: Information and Definition Schemas (Schemata)
• 13: SQL Routines and Types using the Java Programming Language (JRT)
• 14: XML-Related Specification
• 15: Multidimensional arrays (MDA) (Published June 2019)
• 16: Property Graph Query (SQL/PGQ) - Apparently being worked on for SQL:2020?

 

Part 16 will define how GQL (Graph Query Language) will be made accessible to SQL. If such a part will be added is (as of July 2019) still being discussed.

Part 2

The largest and most importand part is part 2: foundations. In SQL:2011, it had 1470 pages.

 

It covers

• Data types
• DDL
• Querying: select, join etc.
• DML: insert, update, delete, merge …
• Access Control: grant, revoke
• Transaction control: commit, rollback, …
• Connection management: connect, disconnect
• Session management: set session
• Exception handling: get diagnostics

Mandatory and optional features

The SQL standard covers a list of features which are put into one of categories

• mandatory
• optional

 

These features are listed in Annex F of each part of the standard.

 

An SQL implementation is called conformant if it implements all mandatory features.

It might add any optional feature.

 

Features seem to be numbered with (F|S|T)\d\d\d (for example F314 is merge statement with DELETE branch).

Not freely publicly available

ISO and/or(?) IEC only publishes the informative sections of the standard publicly and freely. The rest needs to be purchased.

Technical reports

Starting in 2011, the SQL Standards committees have published 7 (as of 2020-02) technical reports. They don't belong to the standard but are nevertheless a good read.

 

The technical reports include:

• ISO/IEC TR 19075-1: XQuery Regular Expression Support in SQL
• ISO/IEC TR 19075-2: SQL Support for TimeRelated Information
• ISO/IEC TR 19075-3: SQL Embedded in Programs using the Java programming language
• ISO/IEC TR 19075-4: SQL with Routines and types using the JavaTM programming language
• ISO/IEC TR 19075-5: Row Pattern Recognition in SQL
• ISO/IEC TR 19075-6: SQL support for JSON
• ISO/IEC TR 19075-7: SQL Support for Polymorphic Table Functions

 

The next(?) technical report seems to be:

• ISO/IEC TR 19075-8: SQL Support for multi dimensional arrays

 

These technical reports are free for single use and can be downloaded here.

Misc

The SQL standard does not have a create database statement. In fact, the SQL standard does neither define nor use the word database (except for constructs like «database application» or «database access» and in its title).

 

The SQL standard also does not have a create index statement.

TODO

X3H2 is the ANSI database committee.

See also

Notes about the history of SQL.

Links

Ron Savage's github repository SQL contains the BNF Grammars for SQL-92, SQL-99 and SQL-2003.

 https://renenyffenegger.ch/notes/misc/ISO/9075/