Database Management SystemsSQL Overview

SQL Overview

LevelBeginner

Duration75 mins

TopicSQL Overview

2 / 5

SQL Standards

The Foundation of Interoperability

When you write a SQL query, you might assume that the same query will work identically across Oracle, PostgreSQL, SQL Server, and MySQL. In theory, that should be true—SQL is a standardized language. In practice, the reality is far more nuanced.

Understanding SQL standards is essential for writing portable code, evaluating database products, and navigating the complex landscape of vendor-specific extensions. Standards define not just syntax, but semantics—what SQL should do, not just how to write it.

This page explores the SQL standardization process, the major standard versions, conformance levels, and the practical implications for your work.

What You Will Learn

By the end of this page, you will understand how SQL standards are developed and ratified, the major milestones in SQL standardization history, what conformance levels mean, and how to navigate the gap between standard SQL and vendor-specific dialects.

The Standards Bodies

SQL standardization is a formal process involving recognized international standards organizations. Understanding who creates these standards helps you appreciate their authority and limitations.

ANSI (American National Standards Institute):

ANSI is the United States' representative body to ISO and IEC. It oversees the development of voluntary consensus standards for products, services, processes, and systems in the US. ANSI's Database Committee (NCITS H2, formerly X3H2) has been responsible for SQL standards development since the 1980s.

ISO (International Organization for Standardization):

ISO is an independent, non-governmental international organization with 167 member countries. ISO/IEC JTC 1/SC 32 (Data Management and Interchange) maintains the international SQL standard. ANSI and ISO coordinate to produce identical SQL standards—what's called a 'dual-logo' standard.

How SQL Standards Are Developed

•Proposal Phase — Committee members (often database vendor representatives) propose new features or changes
•Working Draft — Technical experts develop detailed specifications through iterative review
•Committee Draft (CD) — Draft is circulated for broader comment within the standards committee
•Draft International Standard (DIS) — Near-final version circulated to ISO member bodies for vote
•Final Draft International Standard (FDIS) — Final voting stage before publication
•International Standard (IS) — Published as ISO/IEC 9075 with formal status

The Standards Development Cycle

A major SQL standard revision typically takes 3-5 years from initial proposal to publication. This deliberate pace ensures thorough review but means standards often lag behind vendor implementations. Vendors frequently implement features before they're standardized, creating tension between innovation and portability.

The ISO/IEC 9075 Document Structure:

The SQL standard is not a single document but a multi-part specification covering different aspects of the language:

ISO/IEC 9075 Parts (SQL:2023)
Part	Title	Description
Part 1	SQL/Framework	Overview and common definitions
Part 2	SQL/Foundation	Core SQL: data types, schemas, queries, DML
Part 3	SQL/CLI	Call-Level Interface (programmatic access)
Part 4	SQL/PSM	Persistent Stored Modules (procedures, functions)
Part 9	SQL/MED	Management of External Data (foreign tables)
Part 10	SQL/OLB	Object Language Bindings (embedded SQL)
Part 11	SQL/Schemata	Information schema and definition schema
Part 13	SQL/JRT	SQL Routines using Java (deprecated)
Part 14	SQL/XML	XML-related specifications
Part 15	SQL/MDA	Multi-dimensional arrays
Part 16	SQL/PGQ	Property Graph Queries

The Core Standard

For most developers, Part 2 (SQL/Foundation) is the heart of the standard—it defines the SELECT, INSERT, UPDATE, DELETE statements, data types, joins, subqueries, and virtually everything you use daily. The other parts address specialized capabilities or legacy interfaces.

Evolution of SQL Standards

The SQL standard has evolved dramatically since 1986, each version adding capabilities while maintaining backward compatibility. Understanding this evolution helps you recognize which features are widely supported versus which are cutting-edge.

SQL-86 (SQL1): The Foundation

The first standard was relatively minimal, formalizing what System R and early commercial databases already implemented:

Basic SELECT...FROM...WHERE queries
INSERT, UPDATE, DELETE statements
Schema definition (CREATE TABLE, CREATE VIEW)
Simple data types (CHARACTER, NUMERIC, INTEGER, etc.)
Basic referential integrity

SQL-89: Minor Enhancements

A minor revision that clarified ambiguities and added:

Referential integrity actions (foreign keys with CASCADE)
Embedded SQL improvements
Minor syntax clarifications

SQL-92: The Modern Foundation

SQL-92 (also called SQL2) is the most significant revision in SQL history. It expanded the standard from ~100 pages to over 600 pages and defined the SQL syntax that developers still use daily. When people discuss 'standard SQL,' they often mean SQL-92 core features.

SQL-92: The Watershed Standard

SQL-92 introduced transformative features that defined modern SQL:

SQL-92 Major Additions

•Explicit JOIN Syntax — INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN with ON clauses
•CASE Expressions — Conditional logic within queries
•String Operations — SUBSTRING, TRIM, UPPER, LOWER, concatenation operator
•Date/Time Types — Standardized DATE, TIME, TIMESTAMP, INTERVAL
•Domain Definitions — User-defined domains for type abstraction
•Scrollable Cursors — Navigate result sets in any direction
•Temporary Tables — Session-scoped temporary storage
•Information Schema — Standard metadata views

SQL-92 JOIN Syntax Revolution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Pre-SQL-92: Implicit join in WHERE clause
SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.id
  AND e.salary > 50000;
 
-- SQL-92: Explicit JOIN syntax
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000;
 
-- SQL-92: Outer joins with standard syntax
SELECT e.name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.id;
 
-- SQL-92: CASE expressions for conditional logic
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 50000 THEN 'Mid-Level'
        ELSE 'Junior'
    END AS level
FROM employees;

SQL:1999 (SQL3): Object-Relational Extensions

SQL:1999 brought object-oriented concepts into the relational world:

SQL:1999 Major Additions

•User-Defined Types (UDTs) — Create custom data types with methods
•Structured Types — Complex types with attributes (object-like)
•Recursive Queries — WITH RECURSIVE for hierarchical data
•Triggers — Automatic actions on data changes
•Roles — Named collections of privileges for easier security management
•Regular Expressions — SIMILAR TO and LIKE pattern matching
•OLAP Functions — GROUPING SETS, CUBE, ROLLUP for analytics

SQL:1999 Recursive Query Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Find all employees in a management hierarchy
-- Recursive CTE introduced in SQL:1999
 
WITH RECURSIVE management_chain AS (
    -- Base case: top-level manager
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees reporting to current level
    SELECT e.id, e.name, e.manager_id, mc.level + 1
    FROM employees e
    INNER JOIN management_chain mc ON e.manager_id = mc.id
)
SELECT * FROM management_chain
ORDER BY level, name;
 
-- This was impossible in standard SQL before SQL:1999
-- Each vendor had proprietary solutions (CONNECT BY in Oracle, etc.)

Modern SQL Standards (2003-2023)

The 21st century brought continued SQL evolution, addressing new data paradigms and analytical needs.

SQL:2003 — The Analytic Revolution

SQL:2003 introduced capabilities that transformed SQL from a simple query language into a powerful analytical tool:

SQL:2003 Transformative Features

•Window Functions — ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD() with OVER() clause
•MERGE Statement — Upsert operations combining INSERT, UPDATE, DELETE
•XML Support — SQL/XML for native XML handling
•Sequence Generators — Standard CREATE SEQUENCE for auto-increment values
•Enhanced OLAP — Additional analytical functions and grouping capabilities
•Identity Columns — Standard way to auto-generate primary keys

SQL:2003 Window Functions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Window functions revolutionized analytical SQL
-- Analyze data within partitions without self-joins
 
-- Rank employees within each department by salary
SELECT 
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS salary_rank_in_dept,
    salary - LAG(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS gap_to_previous
FROM employees;
 
-- Calculate running totals and moving averages
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS weekly_moving_avg
FROM orders;

SQL:2011 — Temporal Data

SQL:2011 addressed a long-standing challenge: managing time-varying data.

SQL:2011 Temporal Features

•System-Versioned Tables — Automatic tracking of data history (system time)
•Application Time — Business-defined validity periods
•Bitemporal Tables — Combined system and application time tracking
•Period Definitions — Formal specification of time ranges
•Temporal Queries — FOR SYSTEM_TIME AS OF, FOR SYSTEM_TIME BETWEEN

SQL:2011 Temporal Tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- System-versioned temporal table
-- Database automatically tracks all historical states
 
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    
    -- System time tracking
    sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (sys_start, sys_end)
) WITH SYSTEM VERSIONING;
 
-- Query current data (default)
SELECT * FROM employees;
 
-- Query data as it was on a specific date
SELECT * FROM employees
FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-01 00:00:00';
 
-- Query complete history of changes
SELECT * FROM employees
FOR SYSTEM_TIME BETWEEN 
    TIMESTAMP '2023-01-01 00:00:00' AND 
    TIMESTAMP '2024-01-01 00:00:00';

SQL:2016 — JSON and Pattern Matching

SQL:2016 acknowledged the rise of JSON in web applications:

SQL:2016 Key Features

•JSON Data Type — Native JSON storage and validation
•JSON Functions — JSON_VALUE, JSON_QUERY, JSON_TABLE for extraction and transformation
•JSON Path Language — Standard syntax for navigating JSON structures
•Row Pattern Recognition — MATCH_RECOGNIZE for complex event processing
•Polymorphic Table Functions — Functions returning tables with dynamic schemas

SQL:2023 — Property Graphs

The latest major standard brings graph database capabilities to SQL:

SQL:2023 Innovations

•SQL/PGQ — Property Graph Queries integrated into SQL
•GRAPH_TABLE — Express graph patterns returning tabular results
•Graph Pattern Matching — Navigate relationships with pattern syntax
•Enhanced JSON — Additional JSON functions and improved path language
•Multi-dimensional Arrays — Enhanced array operations (SQL/MDA)

Conformance Levels and Features

Not every database implements every SQL standard feature. To address this reality, the SQL standard defines conformance levels—graduated tiers of feature support.

The Core Conformance Concept:

Starting with SQL:1999, the standard defines:

Core SQL — The fundamental features every conforming implementation must support
Optional Features — Additional capabilities identified by feature codes (e.g., F311, T131)

A database claiming 'SQL conformance' must support Core SQL. Beyond that, vendors choose which optional features to implement.

SQL Conformance Levels (Historical SQL-92 View)
Level	Features	Target Audience
Entry	Basic SELECT, INSERT, UPDATE, DELETE; simple data types; basic constraints	Minimal compliance; legacy systems
Intermediate	Entry + domains, assertions, more data types, enhanced joins	Production systems requiring robust features
Full	Intermediate + complete standard implementation	Maximum portability; rarely achieved

The Reality of Conformance

No major commercial database fully implements the SQL standard. Even PostgreSQL, known for standards compliance, has gaps and deviations. The standard is a reference point, not a rigid requirement enforced by any authority.

Feature Packages:

Modern SQL standards organize optional features into logical packages. Here are examples of feature packages from SQL:2023:

Selected SQL Optional Feature Packages
Package	Features Included	Database Support
Core SQL	Basic queries, joins, constraints, transactions	All major databases
Enhanced datetime facilities	INTERVAL arithmetic, timezone support, precision control	Most major databases
Active database (triggers)	BEFORE/AFTER triggers, ROW/STATEMENT level	Oracle, PostgreSQL, SQL Server
OLAP facilities	CUBE, ROLLUP, GROUPING SETS, window functions	Most major databases
Temporal tables	System-versioned tables, temporal queries	SQL Server, MariaDB, DB2 (limited elsewhere)
JSON support	JSON data type, path queries, construction	PostgreSQL, MySQL, SQL Server, Oracle

Checking Conformance

Database documentation typically lists SQL standard conformance details. PostgreSQL, for example, maintains an appendix mapping each SQL feature to its implementation status. When writing portable SQL, focus on Core SQL features and widely-implemented optional features.

Vendor Dialects and Extensions

Despite standardization, each major database has developed its own SQL dialect—variations in syntax, additional proprietary features, and different interpretations of standard features. Understanding these dialects is essential for real-world database work.

Why Dialects Exist

•Features implemented before standardization
•Competitive differentiation among vendors
•Performance optimizations for specific use cases
•Legacy compatibility requirements
•Standards lag behind innovation

Coping Strategies

•Prefer standard syntax when available
•Abstract dialect differences in application layer
•Use ORM tools that handle dialect translation
•Document dialect-specific code clearly
•Test across target database platforms

Common Dialect Differences:

Let's examine how common operations differ across major databases:

Dialect Comparison: Common Operations
Operation	Standard SQL	Oracle	SQL Server	MySQL	PostgreSQL
Limit rows	`FETCH FIRST n ROWS`	`FETCH FIRST n ROWS` or `ROWNUM`	`TOP n` or `OFFSET/FETCH`	`LIMIT n`	`LIMIT n` or `FETCH FIRST`
String concat	`\|\|`	`\|\|`	`+`	`CONCAT()`	`\|\|`
Current date	`CURRENT_DATE`	`SYSDATE`	`GETDATE()`	`CURDATE()`	`CURRENT_DATE`
Auto-increment	`GENERATED AS IDENTITY`	`GENERATED AS IDENTITY`	`IDENTITY(1,1)`	`AUTO_INCREMENT`	`SERIAL` or `GENERATED`
Boolean type	`BOOLEAN`	Not native (NUMBER)	`BIT`	`BOOLEAN` or `TINYINT`	`BOOLEAN`
Upsert	`MERGE`	`MERGE`	`MERGE`	`ON DUPLICATE KEY`	`ON CONFLICT`

Dialect Examples: Pagination
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Standard SQL (SQL:2008)
SELECT * FROM products
ORDER BY price
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;
 
-- MySQL / PostgreSQL (common approach)
SELECT * FROM products
ORDER BY price
LIMIT 10 OFFSET 20;
 
-- SQL Server (legacy)
SELECT TOP 10 * FROM products
ORDER BY price;  -- Doesn't support offset natively pre-2012
 
-- Oracle (legacy)
SELECT * FROM (
    SELECT p.*, ROWNUM rn FROM (
        SELECT * FROM products ORDER BY price
    ) p
    WHERE ROWNUM <= 30
)
WHERE rn > 20;
 
-- Notice: Same result, dramatically different syntax

Subtle Semantic Differences

Syntax differences are visible and relatively easy to handle. More dangerous are subtle semantic differences—cases where the same syntax produces different results. For example, NULL sorting order, string collation, and date/time handling vary significantly across databases.

Practical Standards Navigation

Given the gap between standard SQL and real-world databases, how should you approach SQL development? Here are practical strategies for different scenarios.

When to Prioritize Standard SQL

•Multi-database products — If your application must support multiple database backends, standard SQL minimizes porting effort
•Long-term projects — Standard features are more likely to remain stable across database version upgrades
•Team knowledge transfer — Standard SQL is transferable knowledge; proprietary extensions require relearning
•Migration scenarios — Moving between database vendors is easier when queries use standard syntax
•Educational content — Teaching SQL using standard syntax ensures broad applicability

When Vendor Extensions Are Justified

•Performance-critical operations — Vendor-specific optimizations (e.g., Oracle hints) may be necessary
•Single-database commitment — If you'll never migrate, leverage all available features
•Unique capabilities — PostgreSQL's advanced data types, SQL Server's CLR integration, etc.
•Existing codebase — Consistency with existing dialect may outweigh portability
•Advanced analytics — Proprietary window function extensions often precede standardization

Layered Abstraction Strategy

A common architectural pattern: use standard SQL for core CRUD operations (which rarely need porting) and confine dialect-specific SQL to a thin database access layer. This way, switching databases requires changing one layer, not the entire codebase.

Testing for Portability:

If SQL portability is a requirement, testing is essential:

Portability Testing Approaches

•Multi-database CI/CD — Run test suites against all target databases in continuous integration
•Docker-based testing — Spin up PostgreSQL, MySQL, SQL Server containers for local testing
•SQL linting — Use tools that flag non-standard syntax (e.g., sqlfluff with appropriate rules)
•Abstraction layer validation — Verify that your ORM/query builder generates valid SQL for each target
•Manual feature matrix — Maintain documentation of which features are used and their portability status

Reading the SQL Standard

The SQL standard documents are publicly accessible (for a fee) through ISO and ANSI, but they are dense, formal specifications—not tutorials. Understanding how to read them is a specialized skill.

Accessing the Standard

ISO/IEC 9075 documents are sold individually (around $150-200 per part) or as a collection. Draft versions are sometimes freely available. Academic institutions often have ISO subscriptions. For most developers, secondary sources (database documentation, books, articles) are more practical than the raw standard.

Standard Document Structure:

Each standard part follows a consistent structure:

SQL Standard Document Elements

•Scope — What the document covers and doesn't cover
•Normative References — Other standards it depends on
•Definitions — Precise terminology definitions
•Concepts — High-level explanations of features
•Common Elements — Shared syntax productions
•Syntax Rules — BNF grammar for SQL statements
•Access Rules — Authorization requirements
•General Rules — Semantic behavior specifications
•Conformance Rules — Implementation requirements

BNF Grammar in SQL Standards:

SQL syntax is formally specified using Backus-Naur Form (BNF). Here's an example from the standard:

<query specification> ::=
    SELECT [ <set quantifier> ] <select list>
    <table expression>

<set quantifier> ::= DISTINCT | ALL

<select list> ::=
    <asterisk>
  | <select sublist> [ { <comma> <select sublist> }... ]

<select sublist> ::=
    <derived column>
  | <qualified asterisk>

This notation precisely defines what constitutes a valid SQL query. Learning to read BNF helps you understand exactly what the standard permits.

Practical Resources

Rather than reading the raw standard, consider: PostgreSQL documentation (known for excellent standards coverage), 'SQL:2011: Temporal Data & the ISO/IEC 9075 Standard' by Kulkarni, and the 'use-the-index-luke.com' website for optimization-focused standards insights.

Summary: SQL Standards Mastery

SQL standardization is a complex but essential topic for serious database work. Let's consolidate the key points:

Key Takeaways

•Standards are developed by ANSI/ISO — Formal processes ensure global consensus, though development is slow
•The standard evolves continuously — From SQL-86 to SQL:2023, each version adds significant capabilities
•SQL-92 defines 'modern' SQL — Explicit joins, CASE expressions, and core syntax most developers use daily
•Recent standards add analytics and JSON — Window functions (2003), temporal tables (2011), JSON (2016)
•Conformance is never complete — No database implements everything; focus on Core SQL and widely-supported features
•Dialects are inevitable — Oracle, SQL Server, PostgreSQL, MySQL each have unique syntax and semantics
•Portability requires strategy — Use standard SQL for core operations; confine extensions to abstraction layers
•Standards knowledge pays dividends — Understanding what's standard helps you write more portable, maintainable SQL

What's Next:

With SQL's history and standards foundation established, we'll now explore the core characteristics that make SQL unique among programming languages. Understanding these characteristics—declarative nature, set-based operations, and strong typing—will deepen your appreciation of SQL's design philosophy.

Page Complete

You now understand how SQL standards are developed, their evolution over time, conformance levels, and the practical challenges of vendor-specific dialects. This knowledge enables you to write more portable SQL and make informed decisions about when to use standard versus proprietary features.

2 / 5

Loading learning content...

Database Management SystemsSQL Overview

SQL Overview

LevelBeginner

Duration75 mins

TopicSQL Overview

2 / 5

SQL Standards

The Foundation of Interoperability

This page explores the SQL standardization process, the major standard versions, conformance levels, and the practical implications for your work.

What You Will Learn

The Standards Bodies

SQL standardization is a formal process involving recognized international standards organizations. Understanding who creates these standards helps you appreciate their authority and limitations.

ANSI (American National Standards Institute):

ISO (International Organization for Standardization):

How SQL Standards Are Developed

•Proposal Phase — Committee members (often database vendor representatives) propose new features or changes
•Working Draft — Technical experts develop detailed specifications through iterative review
•Committee Draft (CD) — Draft is circulated for broader comment within the standards committee
•Draft International Standard (DIS) — Near-final version circulated to ISO member bodies for vote
•Final Draft International Standard (FDIS) — Final voting stage before publication
•International Standard (IS) — Published as ISO/IEC 9075 with formal status

The Standards Development Cycle

The ISO/IEC 9075 Document Structure:

The SQL standard is not a single document but a multi-part specification covering different aspects of the language:

ISO/IEC 9075 Parts (SQL:2023)
Part	Title	Description
Part 1	SQL/Framework	Overview and common definitions
Part 2	SQL/Foundation	Core SQL: data types, schemas, queries, DML
Part 3	SQL/CLI	Call-Level Interface (programmatic access)
Part 4	SQL/PSM	Persistent Stored Modules (procedures, functions)
Part 9	SQL/MED	Management of External Data (foreign tables)
Part 10	SQL/OLB	Object Language Bindings (embedded SQL)
Part 11	SQL/Schemata	Information schema and definition schema
Part 13	SQL/JRT	SQL Routines using Java (deprecated)
Part 14	SQL/XML	XML-related specifications
Part 15	SQL/MDA	Multi-dimensional arrays
Part 16	SQL/PGQ	Property Graph Queries

The Core Standard

Evolution of SQL Standards

SQL-86 (SQL1): The Foundation

The first standard was relatively minimal, formalizing what System R and early commercial databases already implemented:

Basic SELECT...FROM...WHERE queries
INSERT, UPDATE, DELETE statements
Schema definition (CREATE TABLE, CREATE VIEW)
Simple data types (CHARACTER, NUMERIC, INTEGER, etc.)
Basic referential integrity

SQL-89: Minor Enhancements

A minor revision that clarified ambiguities and added:

Referential integrity actions (foreign keys with CASCADE)
Embedded SQL improvements
Minor syntax clarifications

SQL-92: The Modern Foundation

SQL-92: The Watershed Standard

SQL-92 introduced transformative features that defined modern SQL:

SQL-92 Major Additions

•Explicit JOIN Syntax — INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN with ON clauses
•CASE Expressions — Conditional logic within queries
•String Operations — SUBSTRING, TRIM, UPPER, LOWER, concatenation operator
•Date/Time Types — Standardized DATE, TIME, TIMESTAMP, INTERVAL
•Domain Definitions — User-defined domains for type abstraction
•Scrollable Cursors — Navigate result sets in any direction
•Temporary Tables — Session-scoped temporary storage
•Information Schema — Standard metadata views

SQL-92 JOIN Syntax Revolution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Pre-SQL-92: Implicit join in WHERE clause
SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.id
  AND e.salary > 50000;
 
-- SQL-92: Explicit JOIN syntax
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000;
 
-- SQL-92: Outer joins with standard syntax
SELECT e.name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.id;
 
-- SQL-92: CASE expressions for conditional logic
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 50000 THEN 'Mid-Level'
        ELSE 'Junior'
    END AS level
FROM employees;

SQL:1999 (SQL3): Object-Relational Extensions

SQL:1999 brought object-oriented concepts into the relational world:

SQL:1999 Major Additions

•User-Defined Types (UDTs) — Create custom data types with methods
•Structured Types — Complex types with attributes (object-like)
•Recursive Queries — WITH RECURSIVE for hierarchical data
•Triggers — Automatic actions on data changes
•Roles — Named collections of privileges for easier security management
•Regular Expressions — SIMILAR TO and LIKE pattern matching
•OLAP Functions — GROUPING SETS, CUBE, ROLLUP for analytics

SQL:1999 Recursive Query Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Find all employees in a management hierarchy
-- Recursive CTE introduced in SQL:1999
 
WITH RECURSIVE management_chain AS (
    -- Base case: top-level manager
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees reporting to current level
    SELECT e.id, e.name, e.manager_id, mc.level + 1
    FROM employees e
    INNER JOIN management_chain mc ON e.manager_id = mc.id
)
SELECT * FROM management_chain
ORDER BY level, name;
 
-- This was impossible in standard SQL before SQL:1999
-- Each vendor had proprietary solutions (CONNECT BY in Oracle, etc.)

Modern SQL Standards (2003-2023)

The 21st century brought continued SQL evolution, addressing new data paradigms and analytical needs.

SQL:2003 — The Analytic Revolution

SQL:2003 introduced capabilities that transformed SQL from a simple query language into a powerful analytical tool:

SQL:2003 Transformative Features

•Window Functions — ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD() with OVER() clause
•MERGE Statement — Upsert operations combining INSERT, UPDATE, DELETE
•XML Support — SQL/XML for native XML handling
•Sequence Generators — Standard CREATE SEQUENCE for auto-increment values
•Enhanced OLAP — Additional analytical functions and grouping capabilities
•Identity Columns — Standard way to auto-generate primary keys

SQL:2003 Window Functions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Window functions revolutionized analytical SQL
-- Analyze data within partitions without self-joins
 
-- Rank employees within each department by salary
SELECT 
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS salary_rank_in_dept,
    salary - LAG(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS gap_to_previous
FROM employees;
 
-- Calculate running totals and moving averages
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS weekly_moving_avg
FROM orders;

SQL:2011 — Temporal Data

SQL:2011 addressed a long-standing challenge: managing time-varying data.

SQL:2011 Temporal Features

•System-Versioned Tables — Automatic tracking of data history (system time)
•Application Time — Business-defined validity periods
•Bitemporal Tables — Combined system and application time tracking
•Period Definitions — Formal specification of time ranges
•Temporal Queries — FOR SYSTEM_TIME AS OF, FOR SYSTEM_TIME BETWEEN

SQL:2011 Temporal Tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- System-versioned temporal table
-- Database automatically tracks all historical states
 
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    
    -- System time tracking
    sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (sys_start, sys_end)
) WITH SYSTEM VERSIONING;
 
-- Query current data (default)
SELECT * FROM employees;
 
-- Query data as it was on a specific date
SELECT * FROM employees
FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-01 00:00:00';
 
-- Query complete history of changes
SELECT * FROM employees
FOR SYSTEM_TIME BETWEEN 
    TIMESTAMP '2023-01-01 00:00:00' AND 
    TIMESTAMP '2024-01-01 00:00:00';

SQL:2016 — JSON and Pattern Matching

SQL:2016 acknowledged the rise of JSON in web applications:

SQL:2016 Key Features

•JSON Data Type — Native JSON storage and validation
•JSON Functions — JSON_VALUE, JSON_QUERY, JSON_TABLE for extraction and transformation
•JSON Path Language — Standard syntax for navigating JSON structures
•Row Pattern Recognition — MATCH_RECOGNIZE for complex event processing
•Polymorphic Table Functions — Functions returning tables with dynamic schemas

SQL:2023 — Property Graphs

The latest major standard brings graph database capabilities to SQL:

SQL:2023 Innovations

•SQL/PGQ — Property Graph Queries integrated into SQL
•GRAPH_TABLE — Express graph patterns returning tabular results
•Graph Pattern Matching — Navigate relationships with pattern syntax
•Enhanced JSON — Additional JSON functions and improved path language
•Multi-dimensional Arrays — Enhanced array operations (SQL/MDA)

Conformance Levels and Features

Not every database implements every SQL standard feature. To address this reality, the SQL standard defines conformance levels—graduated tiers of feature support.

The Core Conformance Concept:

Starting with SQL:1999, the standard defines:

Core SQL — The fundamental features every conforming implementation must support
Optional Features — Additional capabilities identified by feature codes (e.g., F311, T131)

A database claiming 'SQL conformance' must support Core SQL. Beyond that, vendors choose which optional features to implement.

SQL Conformance Levels (Historical SQL-92 View)
Level	Features	Target Audience
Entry	Basic SELECT, INSERT, UPDATE, DELETE; simple data types; basic constraints	Minimal compliance; legacy systems
Intermediate	Entry + domains, assertions, more data types, enhanced joins	Production systems requiring robust features
Full	Intermediate + complete standard implementation	Maximum portability; rarely achieved

The Reality of Conformance

Feature Packages:

Modern SQL standards organize optional features into logical packages. Here are examples of feature packages from SQL:2023:

Selected SQL Optional Feature Packages
Package	Features Included	Database Support
Core SQL	Basic queries, joins, constraints, transactions	All major databases
Enhanced datetime facilities	INTERVAL arithmetic, timezone support, precision control	Most major databases
Active database (triggers)	BEFORE/AFTER triggers, ROW/STATEMENT level	Oracle, PostgreSQL, SQL Server
OLAP facilities	CUBE, ROLLUP, GROUPING SETS, window functions	Most major databases
Temporal tables	System-versioned tables, temporal queries	SQL Server, MariaDB, DB2 (limited elsewhere)
JSON support	JSON data type, path queries, construction	PostgreSQL, MySQL, SQL Server, Oracle

Checking Conformance

Vendor Dialects and Extensions

Why Dialects Exist

•Features implemented before standardization
•Competitive differentiation among vendors
•Performance optimizations for specific use cases
•Legacy compatibility requirements
•Standards lag behind innovation

Coping Strategies

•Prefer standard syntax when available
•Abstract dialect differences in application layer
•Use ORM tools that handle dialect translation
•Document dialect-specific code clearly
•Test across target database platforms

Common Dialect Differences:

Let's examine how common operations differ across major databases:

Dialect Comparison: Common Operations
Operation	Standard SQL	Oracle	SQL Server	MySQL	PostgreSQL
Limit rows	`FETCH FIRST n ROWS`	`FETCH FIRST n ROWS` or `ROWNUM`	`TOP n` or `OFFSET/FETCH`	`LIMIT n`	`LIMIT n` or `FETCH FIRST`
String concat	`\|\|`	`\|\|`	`+`	`CONCAT()`	`\|\|`
Current date	`CURRENT_DATE`	`SYSDATE`	`GETDATE()`	`CURDATE()`	`CURRENT_DATE`
Auto-increment	`GENERATED AS IDENTITY`	`GENERATED AS IDENTITY`	`IDENTITY(1,1)`	`AUTO_INCREMENT`	`SERIAL` or `GENERATED`
Boolean type	`BOOLEAN`	Not native (NUMBER)	`BIT`	`BOOLEAN` or `TINYINT`	`BOOLEAN`
Upsert	`MERGE`	`MERGE`	`MERGE`	`ON DUPLICATE KEY`	`ON CONFLICT`

Dialect Examples: Pagination
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Standard SQL (SQL:2008)
SELECT * FROM products
ORDER BY price
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;
 
-- MySQL / PostgreSQL (common approach)
SELECT * FROM products
ORDER BY price
LIMIT 10 OFFSET 20;
 
-- SQL Server (legacy)
SELECT TOP 10 * FROM products
ORDER BY price;  -- Doesn't support offset natively pre-2012
 
-- Oracle (legacy)
SELECT * FROM (
    SELECT p.*, ROWNUM rn FROM (
        SELECT * FROM products ORDER BY price
    ) p
    WHERE ROWNUM <= 30
)
WHERE rn > 20;
 
-- Notice: Same result, dramatically different syntax

Subtle Semantic Differences

Practical Standards Navigation

Given the gap between standard SQL and real-world databases, how should you approach SQL development? Here are practical strategies for different scenarios.

When to Prioritize Standard SQL

•Multi-database products — If your application must support multiple database backends, standard SQL minimizes porting effort
•Long-term projects — Standard features are more likely to remain stable across database version upgrades
•Team knowledge transfer — Standard SQL is transferable knowledge; proprietary extensions require relearning
•Migration scenarios — Moving between database vendors is easier when queries use standard syntax
•Educational content — Teaching SQL using standard syntax ensures broad applicability

When Vendor Extensions Are Justified

•Performance-critical operations — Vendor-specific optimizations (e.g., Oracle hints) may be necessary
•Single-database commitment — If you'll never migrate, leverage all available features
•Unique capabilities — PostgreSQL's advanced data types, SQL Server's CLR integration, etc.
•Existing codebase — Consistency with existing dialect may outweigh portability
•Advanced analytics — Proprietary window function extensions often precede standardization

Layered Abstraction Strategy

Testing for Portability:

If SQL portability is a requirement, testing is essential:

Portability Testing Approaches

•Multi-database CI/CD — Run test suites against all target databases in continuous integration
•Docker-based testing — Spin up PostgreSQL, MySQL, SQL Server containers for local testing
•SQL linting — Use tools that flag non-standard syntax (e.g., sqlfluff with appropriate rules)
•Abstraction layer validation — Verify that your ORM/query builder generates valid SQL for each target
•Manual feature matrix — Maintain documentation of which features are used and their portability status

Reading the SQL Standard

The SQL standard documents are publicly accessible (for a fee) through ISO and ANSI, but they are dense, formal specifications—not tutorials. Understanding how to read them is a specialized skill.

Accessing the Standard

Standard Document Structure:

Each standard part follows a consistent structure:

SQL Standard Document Elements

•Scope — What the document covers and doesn't cover
•Normative References — Other standards it depends on
•Definitions — Precise terminology definitions
•Concepts — High-level explanations of features
•Common Elements — Shared syntax productions
•Syntax Rules — BNF grammar for SQL statements
•Access Rules — Authorization requirements
•General Rules — Semantic behavior specifications
•Conformance Rules — Implementation requirements

BNF Grammar in SQL Standards:

SQL syntax is formally specified using Backus-Naur Form (BNF). Here's an example from the standard:

<query specification> ::=
    SELECT [ <set quantifier> ] <select list>
    <table expression>

<set quantifier> ::= DISTINCT | ALL

<select list> ::=
    <asterisk>
  | <select sublist> [ { <comma> <select sublist> }... ]

<select sublist> ::=
    <derived column>
  | <qualified asterisk>

This notation precisely defines what constitutes a valid SQL query. Learning to read BNF helps you understand exactly what the standard permits.

Practical Resources

Summary: SQL Standards Mastery

SQL standardization is a complex but essential topic for serious database work. Let's consolidate the key points:

Key Takeaways

•Standards are developed by ANSI/ISO — Formal processes ensure global consensus, though development is slow
•The standard evolves continuously — From SQL-86 to SQL:2023, each version adds significant capabilities
•SQL-92 defines 'modern' SQL — Explicit joins, CASE expressions, and core syntax most developers use daily
•Recent standards add analytics and JSON — Window functions (2003), temporal tables (2011), JSON (2016)
•Conformance is never complete — No database implements everything; focus on Core SQL and widely-supported features
•Dialects are inevitable — Oracle, SQL Server, PostgreSQL, MySQL each have unique syntax and semantics
•Portability requires strategy — Use standard SQL for core operations; confine extensions to abstraction layers
•Standards knowledge pays dividends — Understanding what's standard helps you write more portable, maintainable SQL

What's Next:

Page Complete

2 / 5