intro
Let's learn everything you need to know about ANSI SQL, the standardized version of the SQL language followed by multiple databases.
The languages used by MySQL, PostgreSQL, SQL Server, Oracle, and others for querying data look very similar, despite having unique features for each database. These languages are typically referred to as "SQL," so there must be some sort of SQL standard. Well, that is exactly the case and the reason why ANSI SQL exists.
In this guide, you will learn more about the ANSI standard SQL specification, how the standardization process works, why it is important, and how to keep up with its evolution.
Let’s dive in!
What Is ANSI SQL?
The expression "ANSI SQL" refers to the standardized version of SQL defined by the American National Standards Institute (ANSI). Still, you must be aware that there is no such thing as “ANSI Standards” since ANSI itself does not develop standards. Instead, it approves standards created by various committees.
However, the term "ANSI SQL" is widely used in the community to refer to the official set of rules and guidelines for structuring SQL queries. That standardization process provides consistency in syntax and functionality across different database management systems (DBMS), whether you are using MySQL, PostgreSQL, SQL Server, Oracle, or others.
The goal behind this standard is to provide a common language for interacting with databases.
History of the SQL Standardization Process
SQL, originally called SEQUEL (whether it should it be pronounced as “SQL” or “SEQUEL” is a topic up for debate to begin with), was developed in the early 70s by Donald Chamberlin and Ray Boyce based on E.F. Codd’s relational model.
Initially, SQL was used only in IBM's "System R," but it then gained popularity after Oracle released its version in 1979. In 1986, ANSI officially accepted SQL, accepting the first standard, ANSI X3.135-1986. That standard laid the foundation for future revisions, which continue to this day.
Over the years, SQL evolved, and since 2003, it has been subdivided into 9 parts, with the most current version, ISO/IEC 9075:2023, still in validity as of this writing. Note that vendors can adapt SQL to their systems and introduce extensions, but most remain aligned with the standard for interoperability. That proves the success of the SQL ANSI standardization process.
Why Is ANSI SQL So Important?
Let's explore the three main reasons why a standardized version of SQL is so important for the database industry.
Consistency and Portability Across Different DBMS Systems
ANSI SQL supports uniformity in SQL syntax and functionality, guaranteeing compatibility across a long list of database management systems. That consistency enables you, in most cases, to write the same queries across different databases without major modifications required.
As a result, standardized SQL also improves portability, making it easier to transfer queries, data, and even entire databases between different DBMSs. That reduces vendor lock-in and simplifies data migration.
Reduced Learning Curve
A standardized SQL language is especially useful in educational settings, like courses, SQL books, and videos, as instructors do not need to teach and explain the syntax for each of the most popular databases individually.
In short, standardization simplifies the learning process for newcomers to SQL, allowing them to focus on mastering one core language. It also guarantees that the skills learned are transferable across different systems, limiting the need to re-learn platform-specific variations.
Increased Language Longevity
ANSI SQL receives updates every few years to keep pace with emerging technologies and the evolving needs of modern databases. For instance, the rise of AI is transforming the IT industry, and new data types (such as MySQL 9’s vector data type) are being introduced to handle embeddings and similar data structures more effectively and efficiently.
These updates are enough to adapt the SQL language to the future, offering solutions to new challenges in data management within databases.
Main Components of the ANSI SQL Standard
The current international standard for SQL (ISO/IEC 9075) has been most recently revised in 2023 and includes 9 parts:
Note that the parts are numbered up to 14, even though there are only 9, to maintain the original structure of the standard.
Since the current standard for SQL is established by ISO (International Organization for Standardization) and then approved by ANSI in the U.S., standardized SQL is also referred to as “ANSI/ISO SQL.”
In particular, ANSI/ISO SQL specifies:
For a detailed overview, refer to “SQL Cheat Sheet: A Comprehensive Guide to SQL Commands and Queries.” Otherwise, take a look at our SQL Cheat Sheet PDF.
Standard ANSI/ISO SQL: Versions and Releases
Explore the key ANSI/ISO SQL versions and releases in the table below:
| Year | Official ANSI Standard | Informal Name | Comments |
|---|---|---|---|
| 1986 | ANSI X3.135:1986 | SQL-86 | First formalized by ANSI, adopted as FIPS PUB 127 |
| 1987 | ISO/IEC 9075:1987 | SQL-87 | First standard approved by ISO |
| 1989 | ANSI X3.135-1989, ISO/IEC 9075:1989, FIPS PUB 127-1 | SQL-89 | Minor revision adding integrity constraints, adopted as FIPS PUB 127-1 |
| 1992 | ANSI X3.135-1992, ISO/IEC 9075:1992, FIPS PUB 127-2 | SQL-92, SQL2 | Major revision (ISO 9075), Entry Level SQL-92, adopted as FIPS PUB 127-2 |
| 1999 | ISO/IEC 9075:1999 | SQL:1999/SQL3 | Added regular expression matching, recursive queries (e.g., transitive closure), triggers, procedural and control-flow statements, nonscalar types (arrays), some object-oriented features (e.g., structured types), and support for embedding SQL in Java (SQL/OLB) and vice versa (SQL/JRT) |
| 2003 | ISO/IEC 9075:2003 | SQL:2003 | Introduced XML-related features (SQL/XML), window functions, standardized sequences, and autogenerated columns (including identity columns) |
| 2006 | ISO/IEC 9075-14:2006 | SQL:2006 | Added Part 14 for SQL-XML integration: supports importing, storing, and manipulating XML data, and integrates queries using XQuery |
| 2008 | ISO/IEC 9075:2008 | SQL:2008 | Legalized ORDER BY outside cursor definitions. Added INSTEAD OF triggers, TRUNCATE statement, and FETCH clause |
| 2011 | ISO/IEC 9075:2011 | SQL:2011 | Added temporal data (PERIOD FOR). Enhanced window functions and FETCH clause |
| 2016 | ISO/IEC 9075:2016 | SQL:2016 | Added row pattern matching, polymorphic table functions, and operations on JSON data stored in character string fields |
| 2019 | ISO/IEC 9075-15:2019 | SQL:2019 | Added Part 15 for multidimensional arrays (MDarray type and operators) |
| 2023 | ISO/IEC 9075:2023 | SQL:2023 | Introduced JSON data type (SQL/Foundation); Added Part 16 for Property Graph Queries (SQL/PGQ) |
Using a Database Client as Protection Against Changes in the SQL Standard
The SQL standard keep evolving— typically with updates every year—by introducing new features and improvements to address emerging database technologies and needs. Keeping up with that pace requires studying the new introductions and modifications each year.
A multi-database client tool like DbVisualizer helps bridge the gap between those changes and the user experience. Sure, the SQL standard evolves slowly, but DbVisualizer updates much more frequently to incorporate the latest changes, whether from the ANSI/ISO standard or from specific database versions.
In detail, the development team behind DbVisualizer continuously updates its SQL editor's autocomplete features, parsing, and formatting capabilities to support new SQL syntax and functionalities from the latest specifications. Additionally, it offers support for over 50+ individual database engines, which often include unique features or extended functionalities beyond the standard.
By adopting a fully-featured database client, you can always work with an up-to-date tool that supports both the evolving SQL standard and database-specific features.
Conclusion
In this blog post, you explored the history behind the process that led to SQL becoming a global standard. If you have ever wondered why SQL is so similar across most databases, now you know the answer. You also discovered that the best way to protect against changes in the ANSI SQL standard is to use a trusted database client like DbVisualizer. Download it for free today!
FAQ
Which databases follow the ANSI SQL standard?
Most major relational databases follow the ANSI SQL standard, including MySQL, PostgreSQL, SQL Server, Oracle, MariaDB, and SQLite. At the same time, the degree of compliance can vary. Some databases, like MySQL, include additional features and extensions beyond the standard, while others, like MariaDB, offer more standard compliance. So, some databases implement SQL extensions for specific use cases, leading to deviations from strict ANSI/ISO SQL compliance.
What is the difference between ISO/IEC and ANSI SQL?
ISO/IEC and ANSI SQL refer to the same standardized SQL language, but the key difference lies in the organizations responsible for the standards. ANSI (American National Standards Institute) sets the standard in the U.S., while ISO/IEC (International Organization for Standardization/International Electrotechnical Commission) governs it internationally. Both versions are largely identical, with updates typically published under both organizations simultaneously.
What is an example of a typical non-standard ANSI SQL query?
A typical non-standard SQL query include a database-specific extension that is not part of the ANSI SQL standard. For example, a query using the SQL LIMIT clause to restrict results:
1
SELECT * FROM products
2
LIMIT 10;
This is non-standard because ANSI/ISO SQL defines FETCH OFFSET` syntax for limiting results and applying offsets.
Where can you find the ANSI/ISO SQL specifications?
The ANSI/SIO SQL specifications can be found on the official websites of ANSI (American National Standards Institute) and ISO (International Organization for Standardization).
Why use a visual database client?
A visual database client, like DbVisualizer, simplifies database management by providing an intuitive interface for querying, browsing, and editing data. It supports multiple database types, offering features like syntax highlighting, autocomplete, and query formatting, which enhance productivity. Grab a 21-day DbVisualizer Pro trial!
