What is SQL?
Definition
SQL (Structured Query Language) is the standard declarative language for defining, querying and changing data in relational databases. A database query written in SQL describes which data you want, and the database decides how to retrieve it. PostgreSQL, MySQL, SQL Server and SQLite all build on the same ISO standard, but each speaks its own dialect with differences in syntax and functions.
Also known as: Structured Query Language, database query, SQL query, sequel
You describe the result, not the route
SQL is declarative. A query never says which file to open, which order to scan tables in or which data structure to walk. It only states what the result should look like. The database's query planner works out the rest, using statistics about the data to choose a join order, an access method and whether to use an index. That is why a query can drop from seconds to milliseconds without a single character changing, simply because someone added a suitable database index.
SQL became an ANSI and ISO standard (ISO/IEC 9075) in the late 1980s and has been extended many times since. In practice every product speaks a dialect. Limiting the number of rows is LIMIT in PostgreSQL, MySQL and SQLite, TOP in SQL Server and FETCH FIRST n ROWS ONLY in the standard. Plain queries move between systems easily; auto-incrementing IDs, date functions and JSON operators usually do not.
The four families of statements
| Family | Statements | Purpose |
|---|---|---|
| DDL (Data Definition) | CREATE, ALTER, DROP | Defines tables, columns and constraints, i.e. the database schema |
| DML (Data Manipulation) | SELECT, INSERT, UPDATE, DELETE | Reads and changes rows |
| DCL (Data Control) | GRANT, REVOKE | Controls who may do what |
| TCL (Transaction Control) | BEGIN, COMMIT, ROLLBACK | Groups statements into one database transaction |
Reading a SELECT with a JOIN
The query below finds categories with fewer than five active products, including categories that have none at all:
SELECT c.name AS category, COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p
ON p.category_id = c.id AND p.is_active = true
GROUP BY c.name
HAVING COUNT(p.id) < 5
ORDER BY product_count;An INNER JOIN would return only rows that match on both sides, so empty categories would quietly disappear. A LEFT JOIN keeps every row from the left table and fills the right-hand columns with NULL where nothing matches. Two details matter here. COUNT(p.id) counts only real products, whereas COUNT(*) would report 1 for an empty category because the join still produces one row for it. And the is_active condition sits in the ON clause on purpose: moved into WHERE, it would discard the NULL rows and silently turn the query back into an inner join.
Logical order differs from written order
A query starts with SELECT on the page, but it is evaluated as FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This explains a whole class of puzzling errors. A column alias defined in SELECT generally cannot be used in WHERE, because it does not exist yet at that stage, but it can be used in ORDER BY. Filtering on an aggregate such as a count needs HAVING, not WHERE.
Mistakes that show up in real code
- Comparing with
= NULL:NULLmeans “unknown” and is not equal to anything, itself included. UseIS NULLorIS NOT NULL. - Concatenating user input into SQL text: this is how SQL injection happens. Pass values as parameters through prepared statements instead.
UPDATEorDELETEwithoutWHERE: it touches every row. For manual changes in production, run the statement inside a transaction, check the affected row count, and only then commit.SELECT *in application code: it ships columns nobody needs and can change behaviour when a column is added later.
SQL is the shared language of relational databases, though several NoSQL systems and data warehouses now offer SQL-like query languages too. Even when an ORM writes the queries for you, reading SQL fluently is the fastest way to understand why one of them is slow.