SQL

First appeared 1974 (as SEQUEL); standardised 1986 · Donald D. Chamberlin

Describe the result, not the steps — the most widely used language in software, by some distance.

Overview

SQL is the declarative language for querying and manipulating relational data, and by most measures the most widely used programming language in existence — it appears in nearly every application that stores data. Its defining characteristic is that you describe the result you want rather than the steps to produce it: the database's query planner decides how to execute it, choosing indexes, join strategies and execution order based on statistics about the actual data. That separation is why a query written twenty years ago can run faster today on the same data without being changed. SQL is standardised by ISO, but every implementation differs meaningfully in its extensions, functions and behaviour around edge cases, so portable SQL is a discipline rather than a default. Modern SQL is far more capable than its reputation suggests, with window functions, common table expressions, recursive queries, JSON support and full-text search all available.

Key facts

The reference details, without the paragraph.

First appeared: 1974 (as SEQUEL); standardised 1986
Designed by: Donald D. Chamberlin and Raymond F. Boyce at IBM
Paradigm: Declarative — you state the result, the planner decides the execution
Typing: Static, per column, with implementation-specific coercion rules
Standard: ISO/IEC 9075, revised roughly every three years
Portability: The core is portable; extensions, functions and edge-case behaviour are not
File extensions: .sql
Major dialects: PostgreSQL, MySQL, SQLite, SQL Server (T-SQL), Oracle (PL/SQL), BigQuery, Snowflake
Execution: Parsed, planned and optimised by the database engine at query time
Licence: The standard is ISO; implementations vary from public domain to proprietary

History

How the language got here — the decisions that still shape how you write it.

SQL was created at IBM's San Jose laboratory in the early 1970s by Donald Chamberlin and Raymond Boyce, as the query language for System R, the first implementation of Edgar Codd's relational model. It was originally called SEQUEL — Structured English Query Language — and renamed because SEQUEL was already a trademark held by a British aircraft company. Codd's 1970 paper had argued that data should be stored in relations and queried by describing what you want, freeing applications from knowing how it was physically arranged; SQL was the practical language that made the idea usable by people who were not mathematicians. Oracle shipped the first commercial SQL database in 1979, beating IBM to market with IBM's own research. Standardisation followed in 1986, and SQL has since survived every predicted replacement: object databases in the 1990s, XML databases in the 2000s, and the NoSQL movement in the 2010s, which was followed by most of those same systems adding SQL interfaces back. Its longevity comes from the durability of the underlying idea — separating what you want from how it is retrieved has outlasted every storage technology it has been implemented on.

1970
Codd's relational model
Edgar Codd publishes a paper arguing data should be stored in relations and queried by describing what you want, freeing applications from knowing how it is physically arranged.
1974
SEQUEL at IBM San Jose
Chamberlin and Boyce design a query language for System R, deliberately readable enough for people who are not mathematicians. It is renamed SQL after a trademark conflict.
1979
Oracle ships first
A small company later renamed Oracle releases the first commercial SQL database, beating IBM to market with IBM's own research.
1986
ANSI standardisation
SQL becomes a formal standard, though vendors continue to extend it in incompatible ways — a tension that persists to this day.
1999
SQL:1999 adds recursion and triggers
Recursive common table expressions arrive, making hierarchies and graphs queryable in pure SQL for the first time.
2003
Window functions
SQL:2003 adds windowing, allowing running totals, rankings and comparisons against neighbouring rows without self-joins or subqueries. It is arguably the largest single improvement in the language's expressiveness.
2010s
NoSQL, and the return
Document and key-value stores are widely predicted to replace SQL. Within a decade most of them add SQL interfaces, and the relational model reasserts itself.
2016–2025
JSON, and analytics engines
SQL:2016 standardises JSON support, and columnar engines such as DuckDB, BigQuery and Snowflake make SQL the default interface for analytics at any scale.

What it is good at

The reasons teams pick it, stated concretely.

Declarative — the planner does the hard part
You describe the result; the optimiser chooses indexes, join order and algorithms from live statistics. A query written years ago can get faster on the same data as the engine improves, without being touched.
Set-based operations at the right layer
Aggregating a million rows in the database and returning fifty is vastly faster than pulling a million rows into application code. Most catastrophic performance problems come from doing in the application what SQL would have done in place.
Extraordinary longevity
Fifty years old, and knowledge transfers across PostgreSQL, SQLite, BigQuery and every engine in between. Few technical skills have held their value this well.
Far more capable than its reputation
Window functions, recursive CTEs, lateral joins, filtered aggregates, JSON operators and full-text search cover problems people routinely export to Python or a script instead.
Real transactional guarantees
ACID properties mean concurrent writers cannot leave the data in an invalid intermediate state. That guarantee is why financial and inventory systems remain relational.

Trade-offs

Every language costs you something. Knowing what, before you commit, is the whole point.

Every dialect differs
String functions, date arithmetic, upsert syntax, limit and offset, and identifier quoting all vary. Portable SQL is achievable but takes discipline, and ORMs exist partly to paper over this.
NULL is three-valued logic
NULL is not a value but an absence, so `NULL = NULL` is unknown rather than true, and `NOT IN` with a NULL in the list returns no rows at all. This trips up nearly everyone at some point.
Performance is opaque until you read the plan
Two queries returning identical results can differ by orders of magnitude. Nothing in the syntax tells you which — you have to read EXPLAIN, which is a separate skill.
Poor composability
Building a query from parts means string concatenation or a query builder, since SQL has no native abstraction for reusable fragments. Views and CTEs help but do not fully solve it.
Awkward to test and version
Stored procedures and complex queries sit outside normal code review and testing workflows. Migrations, fixtures and query tests all require deliberate tooling.

Code examples

Not syntax tours — the idioms that make code read like the language rather than a translation of another one.

Joins and aggregation done in the database

sql

1234567891011121314-- Return the answer, not the raw rows.
SELECT
    a.name                                   AS author,
    COUNT(*)                                 AS book_count,
    ROUND(AVG(r.rating)::numeric, 2)         AS avg_rating,
    COUNT(*) FILTER (WHERE b.year >= 2000)   AS modern_count
FROM authors a
JOIN books b   ON b.author_id = a.id
LEFT JOIN reviews r ON r.book_id = b.id      -- LEFT keeps authors with no reviews
WHERE a.country = 'US'
GROUP BY a.id, a.name
HAVING COUNT(*) >= 3                          -- filters groups, not rows
ORDER BY avg_rating DESC NULLS LAST
LIMIT 10;

WHERE filters rows before grouping, HAVING filters groups after — putting an aggregate in WHERE is an error, and putting a row condition in HAVING is slower. FILTER is a cleaner alternative to `COUNT(CASE WHEN ... END)`.

Window functions — the feature people miss most

sql

123456789101112131415SELECT
    title,
    year,
    rating,
    -- Rank within each year, without a self-join.
    RANK()       OVER (PARTITION BY year ORDER BY rating DESC) AS rank_in_year,
    -- Running total across the whole result.
    SUM(sales)   OVER (ORDER BY year
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
    -- Compare a row with its neighbour.
    rating - LAG(rating) OVER (ORDER BY year)                  AS change,
    -- Aggregate alongside the detail rows, not instead of them.
    AVG(rating)  OVER (PARTITION BY year)                      AS year_avg
FROM books
ORDER BY year, rank_in_year;

A window function computes across related rows while keeping every row in the output — the thing GROUP BY cannot do. Running totals, rankings and period-over-period comparisons all become one pass instead of a correlated subquery.

Recursive CTEs for hierarchies

sql

123456789101112131415WITH RECURSIVE org_chart AS (
    -- Anchor: start at the top.
    SELECT id, name, manager_id, 1 AS depth, name::text AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: join back onto the results so far.
    SELECT e.id, e.name, e.manager_id, oc.depth + 1, oc.path || ' > ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE oc.depth < 10          -- guard against cycles
)
SELECT depth, path FROM org_chart ORDER BY path;

The depth guard is not optional in practice: a cycle in the data — an employee who manages their own manager — will loop forever without it. Recursive CTEs handle trees, graphs, date series and running dependency resolution.

Reading the plan, and fixing the query

sql

1234567891011121314151617-- Always EXPLAIN ANALYZE, not just EXPLAIN: the first shows estimates,
-- the second shows what actually happened.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books WHERE lower(title) = 'dune';

-- Seq Scan on books (cost=0.00..2834.00 rows=1 width=64)
--   Filter: (lower(title) = 'dune')
--   Rows Removed by Filter: 99999

-- Wrapping the column in a function prevents index use. Two fixes:
CREATE INDEX books_title_lower_idx ON books (lower(title));
-- or store a normalised column and index that.

-- Composite index column order matters: this serves
-- WHERE author_id = ? and WHERE author_id = ? AND year > ?,
-- but NOT WHERE year > ? alone.
CREATE INDEX books_author_year_idx ON books (author_id, year DESC);

A function applied to a column makes the index unusable — the engine would have to evaluate it for every row. This single pattern accounts for a large share of unexplained slow queries.

Common pitfalls

The mistakes that cost everyone an afternoon at least once.

Building queries by string concatenation
This is SQL injection, the most consequential vulnerability class in web software. Always use parameterised queries — the value must never become part of the SQL text.
NOT IN with NULLs
If the subquery returns any NULL, `NOT IN` yields no rows at all, because the comparison is unknown rather than true. Use `NOT EXISTS`, which behaves as expected.
Wrapping an indexed column in a function
`WHERE lower(email) = ?` or `WHERE DATE(created_at) = ?` prevents index use and forces a full scan. Create an expression index, or restructure the predicate as a range.
SELECT *
It fetches columns you do not need, breaks when the schema changes, and prevents index-only scans. List the columns explicitly in anything that ships.
Getting composite index order wrong
An index on (a, b) serves queries filtering on a, or on a and b — but not on b alone. Put the most selective equality column first.
Long-running transactions
Holding a transaction open while doing application work keeps locks and blocks vacuum. Keep transactions to the shortest span that preserves the invariant.

In production

Where it is running at scale, and what it is doing there.

Every major bank
Transactional systems of record, where relational guarantees are non-negotiable.
Amazon
Aurora, Redshift and RDS all expose SQL over their storage engines.
Stripe
Financial ledgers and reporting built on relational databases.
Airbnb
Analytics and data warehousing, with SQL as the shared language across teams.

Learning path

A realistic order to learn things in, with something to build at each step.

1
Week 1
The core statements
SELECT, WHERE, ORDER BY, LIMIT, and the join types. Understand what INNER, LEFT and FULL actually do to the row set — draw them if it helps.
Build this: Install PostgreSQL or DuckDB, load a public dataset, and answer twenty questions about it.
2
Week 2
Grouping and NULL
GROUP BY, HAVING, aggregate functions, and the three-valued logic of NULL. Learn why `NOT IN (SELECT ...)` returns nothing when the subquery contains a NULL.
Build this: Write aggregate reports, then deliberately introduce NULLs and observe what breaks.
3
Weeks 3–4
Modern SQL
CTEs, window functions, recursive queries, lateral joins and filtered aggregates. This is where SQL stops being a data-fetching language and becomes an analytical one.
Build this: Rewrite a report you previously did in Python or a spreadsheet as a single query.
4
Month 2
Schema and performance
Normalisation and when to denormalise, index types and column order, EXPLAIN ANALYZE, transactions and isolation levels. Reading a query plan is the skill that separates competent from effective.
Build this: Find the slowest query in a real application, read its plan, and make it fast.
5
Ongoing
Operating a database
Migrations, locking and deadlocks, connection pooling, partitioning, replication, and the specific behaviour of your engine. Dialect knowledge starts mattering here.
Build this: Write a zero-downtime migration that adds an indexed column to a large table.

Ecosystem and tooling

The tools you will end up installing whichever project you join.

Tool	Category	What it does
PostgreSQL	Engine	The most standards-compliant open-source engine, with strong extension support
SQLite	Engine	Embedded, serverless, single-file — the most deployed database in the world
DuckDB	Analytics	In-process columnar analytics; SQL over Parquet and CSV without a server
MySQL / MariaDB	Engine	Widely deployed, particularly in web hosting and older LAMP applications
Flyway / Liquibase / Alembic	Migrations	Schema migration tooling — versioned, reviewable changes rather than ad hoc DDL
dbt	Analytics	Transformations as version-controlled, tested SQL models in a warehouse
pgAdmin / DBeaver / DataGrip	Tooling	Clients for browsing schemas, editing queries and reading plans
sqlfluff	Code quality	A dialect-aware SQL linter and formatter for keeping queries consistent

SQL libraries

Library coverage for SQL is on the way.

The guide above is complete. In the meantime, the catalogues for Python, Java, JavaScript, C and C++ are fully written.

Browse all libraries

Frequently asked

Is SQL a real programming language?

It is Turing-complete with recursive CTEs, but that misses the point. SQL is a declarative language for a specific domain, and it is the most widely used language in software by a wide margin. Treating it as a skill worth developing rather than something an ORM hides is one of the higher-return decisions a developer can make.

Should I use an ORM or write SQL?

Both, deliberately. An ORM is genuinely good at simple CRUD, change tracking and mapping rows to objects. For reporting, analytics and anything with complex joins or window functions, write the SQL — the ORM will either generate something poor or be unable to express it. Knowing SQL is what lets you tell which situation you are in.

Which dialect should I learn?

PostgreSQL. It is the most standards-compliant, so the knowledge transfers well; it is free and excellent; and its documentation is genuinely instructive rather than just a reference. SQLite is a good second for its ubiquity and DuckDB for analytics.

Why is NULL so awkward?

Because it means 'unknown', not 'empty'. Comparing an unknown to anything yields unknown rather than true or false, which is logically consistent but counterintuitive. Use `IS NULL`, `COALESCE`, and `NOT EXISTS` rather than `NOT IN`, and be deliberate about which columns are nullable.

How do I make a slow query fast?

Read EXPLAIN ANALYZE first — guessing wastes time. Look for sequential scans on large tables, rows removed by filter, and estimates that differ wildly from actual counts. Most fixes are an index, removing a function from around an indexed column, or restructuring a correlated subquery as a join.

Did NoSQL not replace SQL?

No, and most of those systems have since added SQL interfaces. Document and key-value stores solved real problems around horizontal scale and flexible schemas, and they remain the right answer for some workloads. But relational guarantees and declarative querying turned out to be worth more than expected, and the industry largely came back.

SQL

Codd's relational model

SEQUEL at IBM San Jose

Oracle ships first

ANSI standardisation

SQL:1999 adds recursion and triggers

Window functions

NoSQL, and the return

JSON, and analytics engines

Declarative — the planner does the hard part

Set-based operations at the right layer

Extraordinary longevity

Far more capable than its reputation

Real transactional guarantees

Every dialect differs

NULL is three-valued logic

Performance is opaque until you read the plan

Poor composability

Awkward to test and version

Building queries by string concatenation

NOT IN with NULLs

Wrapping an indexed column in a function

SELECT *

Getting composite index order wrong

Long-running transactions

Every major bank

Amazon

Stripe

Airbnb

The core statements

Grouping and NULL

Modern SQL

Schema and performance

Operating a database