Back to articles list Articles Cookbook
11 minutes read

The History of SQL Standards

Author's photo
Agnieszka Kozubek-Krycuń

Has the SQL standard changed in the 30+ years it's been around? Absolutely! Learn about the journey from SQL-86 to modern SQL, today’s standard data language.

SQL was created in the early 1970s by IBM. In 1974, Donald Chamberlin and Raymond Boyce published the article SEQUEL: A Structured English Query Language, which introduced SQL to the world. The article contained the BNF grammar (a detailed technical description of the language syntax) for the language, which IBM later thought was a mistake. The publication of the grammar allowed different vendors to implement SQL in their own database engines. However, there were quite a lot of variations between vendors’ SQL implementations, as each vendor introduced its own extensions to SQL. By the mid-1980s, substantial incompatibilities arose between the different SQL versions.

Also in the 1980s, the American National Standards Institute (ANSI) started to work on the SQL standard. The goal of introducing a standard was to enable the portability of SQL applications across conforming products.

SQL-86

The first SQL standard was SQL-86. It was published in 1986 as ANSI standard and in 1987 as International Organization for Standardization (ISO) standard. The starting point for the ISO standard was IBM’s SQL standard implementation. This version of the SQL standard is also known as SQL 1.

The SQL-86 standard is 120 pages. It contains the syntax for the SELECT, INSERT, UPDATE, and DELETE statements. The SELECT syntax allows for filtering with WHERE, using GROUP BY with several columns (but not by expressions), and filtering groups with HAVING. There is no explicit JOIN syntax; you can list multiple tables in the FROM clause to get essentially a cross join that you can later filter with WHERE. There is no way to do OUTER JOINs with this syntax. However, the SELECT syntax does allow for subqueries.

The standard also has a schema definition syntax: you can create tables (with the UNIQUE and NOT NULL constraints only), create views, and grant privileges. The data types introduced in the standard are character strings and numbers (NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, and DOUBLE PRECISION). There are no ALTER, DROP, or REVOKE statements. There are also embeddings of SQL into various programming languages (Cobol, Fortran, Pascal, and PL/1).

Generally, you can buy the standard documents from ISO or ANSI, even old and withdrawn versions. However, as Evan Carroll shares in his Stack Overflow post, it’s not easy to get hold of the SQL-86 standard nowadays. But if you’re interested, you can find SQL-86 under the name of FIPS-127.

SQL-89

The next SQL standard was SQL-89, published in 1989. This was a minor revision of the earlier standard, a superset of SQL-86 that replaced SQL-86. The size of the standard did not change.

The most important new features were integrity constraints: in addition to the UNIQUE, NOT NULL, and check options in views that were already available, it added primary keys, foreign keys, and DEFAULT and CHECK constraints. The standard also added new language bindings for C and Ada.

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
  • 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).

SQL standard was divided into three levels of conformance: entry (entry level SQL-92 was similar to SQL-89 with integrity constraints), intermediate, and full.

SQL-92 is the foundation of query language used in relational databases today. When they talk about “SQL”, most people mean SQL-92. Databases that had already existed in 1992 modified their implementation of SQL to be compliant with the standard. Newer databases used the standard as a reference for their implementation.

SQL-92 is still what people teach as “SQL” in schools. And rightfully so – SQL-92 is a very good starting point for learning SQL. For a lot of people, it’s enough to do their everyday work. You can learn advanced features later, when you need them. And most databases support SQL-92. No implementation is 100% compliant with the standard, but the incompatibilities are not that important, especially when you’re just getting started.

At LearnSQL.com, Standard SQL is the most important dialect we teach. We believe that if you learn the standard language, you can use it to work with most databases. It’s the foundation of our beginner-level courses; SQL Basics will teach you the SELECT statement with all the features available in SQL-92, including different kinds of JOIN, GROUP BY, set operations, and subqueries. The course How to INSERT, UPDATE, and DELETE Data in SQL will teach you INSERT, UPDATE, and DELETE statements, while SQL JOINs will let you practice different types of JOINs.

For people interested in creating tables, we recommend our Creating Database Structure learning track. The track consists of five courses that will teach you how to create tables, define constraints, select appropriate data types, and create views using standard SQL syntax, which is easily portable between different database engines.

SQL:1999

SQL:1999 (also called SQL 3) was the fourth revision of the SQL standard. Starting with this version, the standard name used a colon instead of a hyphen to be consistent with the names of other ISO standards. This standard was published in multiple installments between 1999 and 2002.

The first installment of SQL:1999 had five parts:

  • Part 1: SQL/Framework (100 pages) defined the fundamental concepts of SQL.
  • Part 2: SQL/Foundation (1050 pages) defined the fundamental syntax and operations of SQL: types, schemas, tables, views, query and update statements, expressions, and so forth. This part is the most important for regular SQL users.
  • Part 3: SQL/CLI (Call Level Interface) (514 pages) defined an application programming interface for SQL.
  • Part 4: SQL/PSM (Persistent Stored Modules) (193 pages) defined extensions that make SQL procedural.
  • Part 5: SQL/Bindings (270 pages) defined methods for embedding SQL statements in application programs written in a standard programming language.

Three more parts, also considered part of SQL:1999, were published later.

SQL:1999 introduced many important features that are part of modern SQL. Among the most important were Common Table Expressions (CTEs). This is a very useful feature that lets you organize long and complex SQL queries and make them more readable. When the WITH [RECURSIVE] syntax is used, CTEs can also recursively process hierarchical data. You can learn how to write recursive queries in SQL with LearnSQL.com’s interactive Recursive Queries course.

SQL:1999 also introduced OLAP (Online Analytical Processing) capabilities, which includes features that are helpful when preparing business reports. The GROUP BY extensions ROLLUP, CUBE, and GROUPING SETS entered the standard at this time. You can learn them in our GROUP BY Extensions course (or read about the course and the extensions in our course of the month series article for November 2020).

Some minor additions to SQL in SQL:1999 standard include using expressions in ORDER BY, the inclusion of data types for large binary objects (LOB and CLOB), and the introduction of triggers.

The size of the SQL standard grew significantly between 1992 and 1999. The SQL-92 standard had almost 600 pages, but it was still accessible to regular SQL users. Books like A Guide to the SQL Standard by Christopher Date and Hugh Darwen discussed and explained the SQL-92 standard.

Starting with SQL:1999 the standard – now over 2,000 pages – was no longer accessible to regular SQL users. It has become a resource for database experts and database vendors. The standard guides the development of SQL in major databases; it shows which new language features are worth implementing to stay current. It also standardizes the syntax of new SQL features, making sure that major databases implement them in a similar way, using similar syntax and semantics.

The change in the role of the SQL standard is emphasized by the fact that there is no longer an official body that certifies compliance with the standard. Until 1996, the National Institute of Standards and Technology (NIST) data management standards program certified SQL DBMS compliance with the SQL standard. Now, vendors self-certify the compliance of their products.

SQL:2003 and beyond

In the 21st century, the SQL standard has been regularly updated.

The SQL:2003 standard was published on March 1, 2004. Its major addition was window functions, a powerful analytical feature that allows you to compute summary statistics without collapsing rows. Window functions significantly increased the expressive power of SQL. They are extremely useful in preparing all kinds of business reports, analyzing time series data, and analyzing trends. The addition of window functions to the standard coincided with the popularity of OLAP and data warehouses. People started using databases to make data-driven business decisions. This trend is only gaining momentum, thanks to the growing amount of data that all businesses collect. You can learn window functions with our Window Functions course. (Read about the course or why it’s worth learning SQL window functions here.) SQL:2003 also introduced XML-related functions, sequence generators, and identity columns.

After 2004, there were no major ground-breaking additions to the language. The changes in the SQL standard reflected the changes in technology at the time. SQL:2003 introduced XML-related functions to allow for interoperability between databases and XML technologies, which were the hot new thing in the early 2000s. SQL:2006 further specified how to use SQL with XML. It was not a revision of the complete SQL standard, just Part 14, which deals with SQL-XML interoperability.

The next revisions of the standard brought minor enhancements to the language. SQL:2008 legalized the use of ORDER BY outside cursor definitions(!), and added INSTEAD OF triggers, the TRUNCATE statement, and the FETCH clause. SQL:2011 added temporal data and some enhancements to window functions and the FETCH clause.

SQL:2016 added row pattern matching and polymorphic table functions as well as long-awaited JSON support. In the 2010s, JSON replaced XML as the common data exchange format; modern Internet applications use JSON instead of XML as their data format. The emerging NoSQL movement also popularized JSON; document databases store JSON files, and key-value stores are compatible with the JSON format. The SQL standard added JSON support to allow for interoperability with modern applications and new types of databases.

The current SQL standard is SQL:2019. It added Part 15, which defines multidimensional array support in SQL.

What’s Your Future with SQL?

Looking back at over 30 years of SQL history, you can see the different stages of its development. SQL 1 in the 1980s (SQL-86, SQL-89) was the first effort to standardize and formalize the definition of the language. SQL-92 provided the foundation for the stable language that we know today, the one that is now the standard data language for all major data technologies.

SQL:1999 and SQL:2003 provided the foundations for modern SQL, with its addition of window functions, Common Table Expressions, and GROUP BY extensions. After 2003, the language reached the phase of stability and maturity. The core language does not change, but new features are added to enhance the existing language and to support emerging technologies, such as XML or JSON.

infographic

The SQL standard is no longer a reference for regular SQL users. However, the benefits of having a standard are numerous. The standard:

  • Points out which language extensions are important and useful.
  • Guides the development of SQL implementations in databases and other data processing technologies.
  • Provides the common syntax and semantics that most data processing technologies will implement.

The standard does not guarantee that all SQL implementations are perfectly compatible, but it guarantees that they are for the most part aligned and they all develop in the same direction.

At LearnSQL.com, you can learn all modern SQL features with our interactive courses:

So, now that you know the history of SQL, what will your future with this language be? We hope you will learn it and experience for yourself how powerful and efficient SQL is!

Sources:

Tags:

You may also like