Anatomy of a SQL file
SQL (Structured Query Language) is a domain-specific language for managing and querying data held in relational database management systems.
File extensions: .sql
Every part of the example below is labelled and explained. This page is one of 55 annotated tours on AnatomyOf, a free, open-source project by LunarWerx Studios.
What is inside a SQL file
Comment
Single-line (--) or block (/* ... */), ignored by the engine.
A double dash (--) starts a comment that runs to the end of the line. Block comments open with /* and close with */, and may span multiple lines, though most dialects do not allow them to nest. Comments are stripped before parsing, so they carry zero runtime cost. Since SQL scripts are often run as one-off migrations, a well-placed comment explaining *why* a change was made tends to outlive whoever wrote it.
CREATE TABLE
Data Definition Language (DDL) statement that defines a table's shape.
CREATE TABLE is DDL (Data Definition Language): it describes structure rather than manipulating rows. Each column is given a name and a data type (INTEGER, VARCHAR(n), DECIMAL(p,s), DATE, TIMESTAMP, etc.), and the engine enforces that type on every value stored in it. DDL statements are typically auto-committed and, in most engines, cannot be rolled back as easily as ordinary data changes: which is part of why schema changes usually go through migration tooling rather than being run ad hoc.
Constraints
Rules like PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and REFERENCES that guard data integrity.
A PRIMARY KEY uniquely identifies each row; NOT NULL and UNIQUE restrict what a column may hold; CHECK enforces an arbitrary boolean expression like amount >= 0. REFERENCES declares a foreign key, tying a column's values back to a primary key in another table. Constraints are checked on every INSERT or UPDATE, so violations are rejected at write time rather than discovered later during a query. This pushes correctness into the database itself instead of relying solely on application code to get it right.
INSERT
Adds one or more new rows to a table.
INSERT INTO table (columns) VALUES (...) appends new rows; listing the target columns explicitly (rather than relying on column order) keeps the statement readable and resilient to schema changes. A single INSERT can supply multiple VALUES tuples to add several rows in one statement. Columns omitted from the column list fall back to their DEFAULT (or NULL, if no default and no NOT NULL constraint blocks it). Most engines also support INSERT ... SELECT to copy rows from a query result straight into another table.
UPDATE
Modifies existing rows that match a condition.
UPDATE table SET column = value WHERE condition rewrites matching rows in place. The SET clause can update several columns at once, separated by commas, and may reference the row's own current values, e.g. SET amount = amount * 1.1. Omitting the WHERE clause updates every row in the table, which is a classic way to turn a Tuesday afternoon into an incident report. Most teams wrap ad hoc UPDATEs in a transaction specifically so they stay reversible until confirmed correct.
SELECT / ORDER BY / LIMIT
Chooses which columns to return, how to sort them, and how many rows to keep.
SELECT names the columns (or expressions, like COUNT(o.order_id) AS order_count) a query returns; SELECT * returns every column but is generally avoided in application code since it breaks silently when the schema changes. ORDER BY sorts the result set, and LIMIT (or FETCH FIRST / TOP, depending on dialect) caps how many rows come back. These clauses run near the end of a query's logical evaluation order, after filtering and grouping: the engine computes the full result set conceptually, then sorts and trims it, however the query optimizer actually chooses to execute it under the hood.
JOIN
Combines rows from two tables based on a related column.
INNER JOIN ... ON matches rows from two tables where the join condition holds, discarding rows with no match on either side. LEFT JOIN keeps every row from the left table regardless of a match, filling unmatched columns with NULL: useful for "show me all customers, even ones with no orders" queries. Table aliases (FROM customers AS c) keep multi-table queries readable and are required once the same table is joined to itself. A missing or wrong ON condition silently produces a cross join's worth of extra rows, which is a common source of duplicated totals.
WHERE
Filters rows before grouping, using comparison and logical operators.
WHERE filters individual rows before any grouping happens, using operators like =, <>, >, IN, LIKE, and BETWEEN, combined with AND/OR. It cannot reference aggregate functions like SUM(...) directly, that restriction is what HAVING exists for. Because WHERE runs early in logical evaluation, filtering here (rather than after a JOIN produces a huge intermediate result) is usually what lets the query planner use an index instead of scanning every row.
GROUP BY / HAVING
Buckets rows into groups and filters those groups with aggregate conditions.
GROUP BY collapses rows sharing the same value(s) in the given columns into single groups, so aggregate functions like COUNT, SUM, and AVG can be computed per group instead of over the whole table. Every non-aggregated column in SELECT must appear in the GROUP BY list in standard SQL. HAVING is WHERE for groups: it filters based on the aggregate result, e.g. HAVING SUM(o.amount) > 10, after the grouping has already happened. Trying to write that same condition in WHERE fails, since WHERE runs before aggregates exist.
Subquery
A SELECT nested inside another statement.
A subquery is a complete SELECT nested inside another query's WHERE, FROM, or column list, usually wrapped in parentheses. WHERE customer_id IN (SELECT ...) filters the outer query against a set of values produced by the inner one, and a subquery used in FROM is treated like a temporary, unnamed table. A correlated subquery references a column from the outer query and is re-evaluated once per outer row, which can be expensive; an uncorrelated subquery runs once. Common table expressions (WITH name AS (SELECT ...)) often express the same logic more readably.
Transaction
BEGIN / COMMIT / ROLLBACK group statements into one all-or-nothing unit.
BEGIN TRANSACTION (or just BEGIN) opens a unit of work; COMMIT makes every change inside it permanent, and ROLLBACK undoes all of them as if they never ran. This is the "atomicity" in ACID: partial failures do not leave the data half-changed. Many engines auto-commit each statement by default when no explicit transaction is open, so wrapping related INSERT/UPDATE/DELETE statements in one transaction is what makes multi-step changes safe to retry or abort as a single group.
Official SQL site · All languages on AnatomyOf
The interactive tour needs JavaScript. Enable it to hover a callout and trace it into the code.