SQL

SQL#

Structured Query Language is the language type into nine database engines out of ten. Standardized in 1986 (SQL-86), then in dozens of revisions; in practice every database implements its own dialect on the same core.

The Basics#

The four DML verbs that cover most of what an operator does on a relational database. SELECT reads, INSERT adds, UPDATE mutates, DELETE removes. The patterns below are the boilerplate every SQL author writes from memory – worth typing without thinking.

SELECT id, name, email
FROM   users
WHERE  active = true AND created_at >= now() - interval '7 days'
ORDER  BY created_at DESC
LIMIT  20;

INSERT INTO users (name, email) VALUES ('operator', 'operator@example.com');
UPDATE users SET active = false WHERE last_seen < now() - interval '90 days';
DELETE FROM sessions WHERE expires_at < now();

Joins#

The verb that combines rows from multiple tables. INNER keeps rows that match on both sides; LEFT keeps every row from the left and pads the right with NULL; FULL keeps both sides; CROSS produces a Cartesian product; LATERAL lets the right side reference left-side columns.

-- INNER JOIN: rows that match both sides
SELECT u.name, o.total
FROM   users u
JOIN   orders o ON o.user_id = u.id;

-- LEFT JOIN: every row from the left, NULL on the right when no match
SELECT u.name, COUNT(o.id) AS order_count
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.name;

Other joins: RIGHT JOIN, FULL JOIN, CROSS JOIN, LATERAL JOIN.

Aggregation#

GROUP BY collapses many rows into summary statistics. COUNT, SUM, AVG, MIN, MAX are the workhorses; date_trunc buckets time; HAVING filters the post-aggregation result. Aggregation is where reporting queries earn their keep against transactional schemas.

SELECT date_trunc('day', created_at) AS day,
       COUNT(*)                       AS orders,
       SUM(total)                     AS revenue
FROM   orders
WHERE  created_at >= now() - interval '30 days'
GROUP  BY day
ORDER  BY day;

-- Filter aggregated results with HAVING
SELECT user_id, COUNT(*) AS n
FROM   orders
GROUP  BY user_id
HAVING COUNT(*) > 5;

Subqueries and CTEs#

Common Table Expressions are usually clearer than nested subqueries.

WITH recent_orders AS (
  SELECT user_id, total
  FROM   orders
  WHERE  created_at >= now() - interval '30 days'
)
SELECT u.name, SUM(r.total) AS spent
FROM   users u
JOIN   recent_orders r ON r.user_id = u.id
GROUP  BY u.id, u.name
ORDER  BY spent DESC
LIMIT  10;

Recursive CTEs handle hierarchies and graphs.

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

Window Functions#

The feature that distinguishes a SQL author from a SQL beginner. Window functions compute aggregates per row across a frame (running totals, ranks, moving averages, previous-row references) without collapsing the result the way GROUP BY does.

Aggregates that don’t collapse rows, one of SQL’s most powerful features.

SELECT id, user_id, total,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS n,
       SUM(total)   OVER (PARTITION BY user_id) AS user_total,
       LAG(total)   OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total
FROM   orders;

Indexes#

The single biggest determinant of query performance once a table grows beyond a few thousand rows. Most production performance work is “did we add the right index” or “is the planner using the index we added”. Each index speeds reads and slows writes; pick deliberately.

CREATE INDEX idx_orders_user_id    ON orders (user_id);
CREATE INDEX idx_orders_created    ON orders (created_at DESC);
CREATE INDEX idx_orders_user_date  ON orders (user_id, created_at DESC);
CREATE UNIQUE INDEX uniq_users_email ON users (lower(email));

-- Partial index for a common filter
CREATE INDEX idx_active_users ON users (created_at) WHERE active;

Index types vary by database.

B-tree, the default; range and equality.
Hash, equality only, sometimes faster.
GIN / GiST / SP-GiST / BRIN (Postgres), full text, JSONB, geospatial, large-scan.
Bitmap, analytics, low-cardinality columns.

Add only the indexes the query plan actually uses; each one slows writes and consumes space.

Transactions#

The atomic unit of work. BEGIN opens, COMMIT makes changes durable, ROLLBACK discards them. Isolation levels trade consistency for concurrency: read-uncommitted is rare; read-committed is the everyday default; serializable is strictest at the cost of throughput.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or ROLLBACK on error

Isolation levels (weakest to strongest).

Read Uncommitted, can see other transactions’ uncommitted writes.
Read Committed, sees only committed data; the Postgres / SQL Server default.
Repeatable Read, the same query returns the same rows within a transaction.
Serializable, as if transactions ran one at a time.

MVCC databases (Postgres) implement these via row versioning rather than locks; the practical performance characteristics differ from textbook descriptions.

Constraints#

Schema-level enforcement of data invariants. NOT NULL, UNIQUE, CHECK, FOREIGN KEY, and PRIMARY KEY let the database refuse bad writes regardless of which application produced them. Skipping constraints means every client must remember every rule, a losing strategy.

Enforce invariants at the schema level.

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  total       NUMERIC(12, 2) NOT NULL CHECK (total >= 0),
  status      TEXT NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE users ADD CONSTRAINT email_lower UNIQUE (lower(email));

Query Planning#

The diagnostic step before any tuning. EXPLAIN shows the planner’s strategy; EXPLAIN ANALYZE runs the query and reports actual versus estimated rows. Most slow-query investigations end with “the planner thought there were ten rows but there were ten million”, a stale-stats fix.

When a query is slow, look at the plan.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM   users u LEFT JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.name;

What to look for.

Sequential scan on a big table that should be using an index.
Hash / Merge / Nested Loop join and whether sizes match expectations.
Sort spilled to disk, work_mem too small.
Estimate vs. actual rows, bad estimates often mean stale stats (run ANALYZE).

JSON and Arrays#

Most modern databases support semi-structured data first-class.

-- Postgres
CREATE TABLE events (id BIGSERIAL PRIMARY KEY, payload JSONB NOT NULL);
CREATE INDEX ON events USING GIN (payload);

SELECT payload->>'user_id' AS user_id, COUNT(*)
FROM   events
WHERE  payload @> '{"type": "click"}'
GROUP  BY user_id;

Dialects#

Each major engine extends standard SQL with its own features. Postgres has arrays and JSONB; MySQL has ON DUPLICATE KEY UPDATE; SQLite has dynamic types; SQL Server has T-SQL; Oracle has PL/SQL. Standard-leaning code is portable; dialect-leaning code is faster.

Standard SQL is a baseline; production code uses dialect-specific extensions.

PostgreSQL, arrays, JSONB, window functions, CTEs, RETURNING, generated columns, UPSERT (ON CONFLICT).
MySQL, JSON type, ON DUPLICATE KEY UPDATE, LIMIT n, m syntax.
SQLite, almost everything but with quirks (dynamic types, no separate VARCHAR length enforcement).
MSSQL, MERGE, table variables, deep T-SQL extensions.
Oracle, CONNECT BY, packages, PL/SQL.

Standard-leaning code is portable; dialect-specific code is faster.

Beyond SQL#

Even where the storage isn’t relational, SQL keeps showing up.

DuckDB, ClickHouse, BigQuery, Snowflake, columnar analytics with SQL.
Spark SQL, Trino, Presto, SQL on top of distributed data.
MongoDB Atlas SQL, Couchbase N1QL, SQL-flavored access to document stores.

Knowing SQL well pays back across more systems than any other query language.

SQL

Contents