PROVIDER - the shared language of structured data

SQL

SQL has been an ANSI standard since 1986, with ISO/IEC 9075:2023 as the current edition, and describes set queries, window calculations and JSON work declaratively across nearly every relational engine; dialect gaps, optimiser-dependent plans and weak procedural logic are its long-standing boundaries.

Programming Languages & Frameworks United States 2026.11.19
4.8pts

Detailed Notes

Pros 5 items

Proposed by IBM in the 1970s and standardized by ANSI in 1986, with ISO/IEC 9075:2023 as the current edition
Declarative syntax lets 1 SELECT describe filtering, grouping and ordering while the optimiser chooses the plan
The 1999 edition added recursive CTEs and triggers, 2003 added window functions, and 2016 completed JSON support
Major implementations cover more than 5 families including MySQL, PostgreSQL, SQL Server, Oracle and SQLite
The 2023 edition adds property graph queries (SQL/PGQ), so relational and graph data share one language

Cons 4 items

Dialects differ noticeably, so pagination syntax, string functions and type systems all need per-engine adjustment
One statement can vary by 1 to 2 orders of magnitude in runtime across engines, so tuning needs real measurements
No package manager or reuse mechanism, which makes stored procedures almost impossible to port
The set-oriented model expresses row-by-row procedural logic awkwardly, pushing complex business rules into a host language

Service Overview

SQL (Structured Query Language) was proposed by IBM in the 1970s and became an ANSI standard in 1986, with ISO/IEC 9075:2023 as the current edition. It describes what result is wanted rather than how to compute it, leaving the access path to the database optimiser.

In the languages and frameworks category it is the general-purpose language of data: back-end services, analytics and reporting tools all rely on it.

Key Strengths

  • Declarative expression: 1 SELECT covers filtering, grouping, aggregation and ordering with far less code than procedural equivalents
  • One standard: ISO/IEC 9075 keeps basic syntax consistent across engines, lowering the cost of moving between them
  • Continuously extended: window functions, recursive CTEs, JSON and property graph queries are all standardised now
  • Widest ecosystem: nearly every database, BI tool and data platform speaks SQL
  • Optimiser cooperation: statistics and indexes let one statement pick different plans as data volume changes

Product Ecosystem

Standards

SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008, SQL:2011, SQL:2016 and SQL:2023, each adding data types and query capability.

Major implementations

Open source covers MySQL, PostgreSQL, SQLite and MariaDB; commercial covers Oracle Database, SQL Server, DB2 and the cloud data warehouses.

Tools

DBeaver, DataGrip and vendor consoles handle querying, while Flyway, Liquibase and dbt cover migrations, modelling and transformation.

Limitations

  • Dialect fragmentation: pagination, date functions, generated keys and casts differ per engine
  • Optimiser dependence: the same statement can differ by 1 to 2 orders of magnitude in runtime across engines
  • Hard to reuse: stored procedures rarely port, and there is no shared package system
  • Weak procedural model: row loops and state machines are clumsy and usually move to a host language

Use Cases

  • Transactional reads and writes (★★★★★): almost every back end reaches relational data through SQL
  • Analytics and reporting (★★★★★): aggregation, window functions and CTEs do the heavy lifting
  • Warehouse modelling (★★★★): tools such as dbt turn SQL into a versioned transformation layer
  • High-concurrency key lookups (★★★): KV stores or caches fit point reads better

Pricing

Item Price Notes
ISO/IEC 9075 standard text Paid per copy Sold by ISO; the standard page blocks automated access
MySQL / PostgreSQL / SQLite $0 Open-source engines deploy directly
Cloud database instances Billed per size Managed services charge for CPU, storage and traffic

The language is free; cost concentrates in commercial database licences (billed per core or seat) and in tuning effort.

FAQ

  • Standard SQL first or a specific engine? Learn the standard syntax and logical evaluation order first, then the dialect and optimiser quirks of your target engine.
  • How do window functions differ from GROUP BY? GROUP BY collapses rows into one, while window functions keep the original rows and compute ranks or running totals over them.
  • Why is a query still slow with an index? Common causes are functions wrapped around indexed columns, implicit casts, or stale statistics; read the execution plan to locate them. More data options are listed under languages and frameworks.

Code Examples

Ranking within groups using a window function

SELECT
    department,
    employee,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
    AVG(salary) OVER (PARTITION BY department)                 AS dept_avg
FROM employees
WHERE status = 'active'
ORDER BY department, salary_rank;

OVER with PARTITION BY attaches aggregates without collapsing rows.

Walking a hierarchy with a recursive CTE

WITH RECURSIVE org AS (
    SELECT id, manager_id, name, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id, e.name, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT * FROM org ORDER BY depth, id;

WITH RECURSIVE pairs an anchor query with a recursive member, and depth tracks the level.

Community & Resources

Visit SQL Website → https://www.iso.org/standard/76583.html
Visit Website ↗