SQL

SQL stands for Structured Query Language, the standard language for defining, querying and changing data in relational databases. It is specified as ISO/IEC 9075, "Database Language SQL", and the latest edition is SQL:2023. You write it against engines such as PostgreSQL, MySQL, SQL Server and SQLite, and each one implements the standard with its own extensions.

How it works

SQL is declarative. You describe the rows you want, and the database engine decides how to find them. A statement is built from clauses, and a few kinds of statements do most of the work:

• Define structure: CREATE TABLE, ALTER TABLE, DROP TABLE.
• Change data: INSERT, UPDATE, DELETE.
• Read data: SELECT, with WHERE, JOIN, GROUP BY, HAVING and ORDER BY.
• Control transactions: COMMIT and ROLLBACK, which end a transaction.

Text values go in single quotes, and a literal single quote is written twice: 'Dianne''s horse'. Double quotes mark identifiers such as table and column names. Key words are not case-sensitive, and the common convention is upper-case key words with lower-case names.

The standard has been revised several times: SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016 and SQL:2023. Since SQL:1999 it defines individual features, and a subset called Core features must be supplied by every conforming implementation. Engines implement different subsets and add their own syntax, so a query written for one database often needs small changes for another.

This example uses the Python sqlite3 module on SQLite 3.45.1. A left join keeps customers with no orders, and the aggregate functions ignore the missing values:

SELECT c.name, COUNT(o.id) AS orders, SUM(o.total) AS spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY spent DESC;

('Ada', 2, 49.5)
('Linus', 1, 12.25)
('Grace', 0, None)

What does SQL stand for?

SQL stands for Structured Query Language. The standard itself is titled "Database Language SQL" and is published as ISO/IEC 9075.

Common pitfalls

• Comparing to NULL with =: WHERE customer_id = NULL matches nothing, because NULL means unknown and is never equal to anything. In the example data it returned 0 rows, while IS NULL returned 1. Use IS NULL and IS NOT NULL.
• Rows with NULL vanish from <>: WHERE customer_id <> 1 returned 1 row out of 4, because the row with a NULL customer is neither equal nor unequal. Add OR customer_id IS NULL when you want it.
• Building queries with string concatenation: a value such as x'; DROP TABLE customers; -- can rewrite the statement. Pass values as bound parameters (WHERE name = ?); in the same test the bound query treated that text as a plain value and matched 0 rows.
• Assuming LIMIT is standard: LIMIT and OFFSET are PostgreSQL-specific syntax also used by MySQL. The standard form since SQL:2008 is FETCH FIRST n ROWS ONLY. SQLite rejects it: after ORDER BY id the error is near "FETCH": syntax error.
• Identifier case surprises: PostgreSQL folds unquoted names to lower case, while the standard says upper case. A column created as "UserId" must then be quoted every time.
• Relying on row order: without ORDER BY, the engine may return rows in whatever order is fastest.

Related terms

• SQLite — an embedded database engine that implements most of SQL.
• ACID — the transaction guarantees that SQL databases provide.
• Primary key — the column that uniquely identifies each row in a table.
• JSON — a data format that many SQL engines can now store and query.
• UUID — a common choice of identifier for a primary key column.

See also

• Tool: SQL to Prisma Schema — converts SQL CREATE TABLE statements into Prisma ORM schema models
• Cheatsheet: SQL Quick Reference — quick lookup for clauses, joins and functions