SQL essentials

SQL is the language you use to ask a relational database for rows, and to change those rows without destroying the rest of the table. The examples below are written so they run on PostgreSQL and on MariaDB or MySQL, which is what this site uses for arcade scores. Where the engines disagree, the note says so. The toy schema is a shop: customers place orders, and an order has a status and a total in pounds.

CREATE TABLE customers (
  id BIGINT PRIMARY KEY,
  email VARCHAR(320) NOT NULL,
  country CHAR(2) NOT NULL
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NULL,
  status VARCHAR(20) NOT NULL,
  total_gbp NUMERIC(12, 2) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

customer_id is nullable on purpose. A NULL there means "we do not know who this order belongs to", which is different from a real customer, and it is the source of several gotchas later.

SELECT, WHERE, ORDER BY, and LIMIT

SELECT chooses columns. FROM chooses the table. List the columns you need. SELECT * is acceptable while you are exploring, and a liability in application code, because a new column changes the shape of every result and can drag a large text field across the network.

SELECT id, email, country
FROM customers
WHERE country = 'GB'
ORDER BY email ASC
LIMIT 20 OFFSET 0;

WHERE keeps rows for which the predicate is true. SQL uses three-valued logic: a comparison can be true, false, or unknown. WHERE keeps only true. AND binds more tightly than OR, so country = 'GB' OR country = 'IE' AND status = 'paid' does not mean what the spacing suggests. Put parentheses around the OR.

ORDER BY is the only way to make "first" mean something. Without it, the engine may return rows in index order, insertion order, or whatever the last plan felt like, and that order can change after an ANALYZE or an upgrade. The default direction is ASC.

NULL sorts differently per engine. PostgreSQL puts NULL last in ASC and first in DESC, and you can override that with NULLS FIRST or NULLS LAST. MariaDB and MySQL treat NULL as the lowest value, so NULL comes first in ASC and last in DESC, and they reject the NULLS FIRST syntax. If you need a portable "nulls last" on MariaDB, sort by a boolean first:

SELECT id, email
FROM customers
ORDER BY email IS NULL, email;

email IS NULL is 1 for nulls and 0 otherwise, so the nulls fall to the end before email is considered.

LIMIT without ORDER BY returns an arbitrary subset. Always pair them. Prefer LIMIT 20 OFFSET 40. MySQL also accepts LIMIT 40, 20, where the first number is the offset, not the count. Large offsets still walk the skipped rows.

Joins

A join combines rows from two tables. The ON clause says which rows belong together. Put the match there, not in WHERE, so that outer joins keep the meaning you wrote.

INNER JOIN keeps only pairs that match. A customer with no orders disappears. An order whose customer_id is NULL disappears, because NULL equals nothing.

SELECT o.id, c.email, o.total_gbp
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'paid';

LEFT JOIN keeps every row from the left table. When the right table has no match, its columns are NULL. This is how you list customers including those who have never ordered:

SELECT c.email, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

Filtering the right-hand table in WHERE turns the left join back into an inner join. WHERE o.status = 'paid' throws away customers whose o.status is NULL, which is every customer with no matching order. If you want "customers, and any paid orders they have, keeping customers with none", the status test belongs in ON:

SELECT c.email, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid';

The anti-join, "customers with no orders", is a left join plus a test that the right key is NULL:

SELECT c.id, c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

RIGHT JOIN is a left join with the tables swapped. Prefer the left form so the preserved table is the one you read first. FULL OUTER JOIN exists in PostgreSQL and not in MySQL or MariaDB.

GROUP BY, HAVING, and aggregates

GROUP BY collapses rows that share a key into one row per key. Every column in the SELECT list must be either in the GROUP BY or inside an aggregate (COUNT, SUM, AVG, MIN, MAX). PostgreSQL enforces this. MySQL does too when ONLY_FULL_GROUP_BY is on, which has been the default since 5.7. Do not rely on the old loose mode that picked an arbitrary email for a grouped customer_id.

WHERE filters rows before the grouping. HAVING filters groups after the aggregates exist. You cannot write WHERE SUM(total_gbp) > 1000. Repeat the aggregate in HAVING rather than using the select alias if you want the same query on PostgreSQL and MariaDB.

SELECT customer_id, SUM(total_gbp) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total_gbp) >= 1000
ORDER BY revenue DESC;

COUNT(*) counts rows. COUNT(customer_id) counts rows whose customer_id is not NULL, so it is smaller when some orders are anonymous. SUM and AVG skip NULL. If every value in the group is NULL, SUM returns NULL, not zero. Wrap it when the caller wants a number: COALESCE(SUM(total_gbp), 0). AVG of an empty group is NULL for the same reason.

DISTINCT is not an aggregate, but people reach for it when they meant GROUP BY. SELECT DISTINCT country is the right tool for "the set of countries". It is the wrong tool for "revenue per customer", because it cannot sum.

Subqueries and CTEs

A subquery is a query in parentheses used as a value, a list, or a table. A scalar subquery must return one column and at most one row. IN takes a list. EXISTS asks whether any row comes back, and it can stop at the first.

SELECT email
FROM customers
WHERE id IN (
  SELECT customer_id FROM orders WHERE status = 'paid'
);

NOT IN is the trap. If the subquery produces even one NULL, the predicate is unknown for every outer row, and the query returns nothing. Anonymous orders make customer_id NULL, so "customers who have never ordered" written with NOT IN goes empty. Use NOT EXISTS, which treats NULL as "this row does not match" rather than "the whole test is unknown":

SELECT c.email
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
);

A common table expression names a subquery for the rest of the statement. It does not, by itself, make the query faster. It makes it readable, and you can reuse the name.

WITH paid AS (
  SELECT customer_id, SUM(total_gbp) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.email, paid.revenue
FROM paid
INNER JOIN customers AS c ON c.id = paid.customer_id
WHERE paid.revenue >= 1000;

PostgreSQL 12 and newer may inline a CTE the way it would a view. AS MATERIALIZED forces it to run separately. MariaDB (from 10.2) and MySQL 8 support WITH, without that hint. A recursive CTE (WITH RECURSIVE) is for trees. A join is the better tool when the relationship is one step deep.

Window functions

A window function computes a value across a set of rows related to the current row, and it does not collapse those rows the way GROUP BY does. You still get one output row per input row. The window is the OVER clause. PARTITION BY splits the window into groups. ORDER BY inside OVER gives the function a sequence, which ranking and running totals need.

SELECT
  id,
  customer_id,
  total_gbp,
  SUM(total_gbp) OVER (PARTITION BY customer_id) AS customer_total,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
  ) AS recent_rank
FROM orders;

customer_total repeats on every order for that customer. recent_rank is 1 for their latest order. Filtering to rank 1 in the same SELECT is not allowed, because WHERE runs before windows. Wrap the query: SELECT * FROM ( ... ) ranked WHERE recent_rank = 1.

ROW_NUMBER is unique even when totals tie. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK does not skip (1, 2, 2, 3). Use ROW_NUMBER when you want exactly one "latest" row and you do not care which tied row wins. Add a unique column to the ORDER BY (created_at DESC, id DESC) so the winner is stable.

Window functions need PostgreSQL, MariaDB 10.2 or newer, or MySQL 8. They are not in MySQL 5.7. SUM(...) OVER (ORDER BY created_at) is a running total, from the start of the partition through the current row. Omit the ORDER BY and every row shows the same sum.

INSERT, UPDATE, and DELETE, safely

Name the columns. An INSERT that relies on column order breaks the day someone adds a column in the middle.

INSERT INTO customers (id, email, country)
VALUES (10, 'ada@example.com', 'GB');

Several rows can share one VALUES list. INSERT ... SELECT copies from a query. PostgreSQL and MariaDB 10.5+ can add RETURNING id. MySQL cannot.

UPDATE and DELETE without a WHERE affect every row. The habit that prevents that is to write the SELECT first, look at the rows, and only then change the verb.

-- preview
SELECT id, status FROM orders WHERE id = 1042 AND status = 'draft';

BEGIN;
UPDATE orders
SET status = 'void'
WHERE id = 1042 AND status = 'draft';
-- if the row count is not 1, ROLLBACK;
COMMIT;

MySQL and MariaDB run in autocommit unless you BEGIN. A second statement after a successful UPDATE is already durable if you forgot the transaction. ROLLBACK only helps before COMMIT.

DELETE FROM orders WHERE id = 1042 removes that row and, if a foreign key says so, either fails or cascades. TRUNCATE is not a faster DELETE. On MySQL and MariaDB it is DDL: it implicitly commits, it cannot sit inside the transaction you meant to roll back, and it resets auto-increment. On PostgreSQL, TRUNCATE can roll back, but it still takes a stronger lock than deleting a few rows. For a handful of rows, DELETE with a WHERE is the safe tool.

Indexes, and why they matter

An index is a sorted structure, usually a B-tree, that lets the engine find matching rows without reading the whole table. It speeds WHERE, JOIN, and ORDER BY when the planner can walk the index in the order the query needs. It costs space, and it slows writes, because every INSERT and relevant UPDATE maintains it.

A composite index is used from the left. This index serves "this customer's orders, newest first":

CREATE INDEX orders_customer_created
  ON orders (customer_id, created_at);

It helps WHERE customer_id = 10 and WHERE customer_id = 10 ORDER BY created_at. It does not help WHERE created_at > ? on its own, because created_at is not the leftmost column and the engine would have to look at every customer. It does not help WHERE LOWER(email) = 'ada@example.com' either: a function around the column hides a plain index unless you index the expression (PostgreSQL and MariaDB support expression indexes; keep the expression identical to the query).

LIKE 'ada%' can use a B-tree on email. LIKE '%ada' cannot, because the leading wildcard means the sort order of the index does not group the matches. A very low-cardinality column, such as a boolean is_active on a table where almost everyone is active, often is not worth its own index. The planner will ignore it and read the table.

Read the plan before and after. EXPLAIN shows the chosen plan. EXPLAIN ANALYZE (PostgreSQL, and MariaDB) runs the query and adds real timings, so do not point it at a destructive statement. "Index added, query still slow" is usually a missing left prefix, a function on the column, or a SELECT that returns so many rows that the table read dominates anyway.

The first index to add is the one that matches the WHERE and the join keys you actually filter on.

NULL gotchas

NULL means unknown, not zero, not false, and not an empty string. Almost every surprising SQL result is this fact in a different costume.

• WHERE phone = NULL matches nothing. The predicate is unknown. Write WHERE phone IS NULL.
• NULL = NULL is unknown, not true. Two unknown phone numbers are not known to be the same.
• NULL + 1 is NULL. A missing total plus a fee is still missing. Use COALESCE(total_gbp, 0) + fee when you mean zero.
• COUNT(phone) skips NULL. COUNT(*) does not.
• SUM of an all-NULL group is NULL.
• NOT IN (SELECT ...) collapses to no rows if the subquery contains a NULL. Use NOT EXISTS.
• A UNIQUE index allows many NULLs in PostgreSQL, MariaDB, and MySQL, because NULL is not equal to NULL. PostgreSQL 15 can opt into UNIQUE NULLS NOT DISTINCT if you want a single NULL. Do not assume the index enforces "only one blank".
• In MySQL, CONCAT(NULL, 'x') is NULL. An empty string is not NULL, and a NOT NULL column will happily store ''.
• A left join produces NULL in the right-hand columns when there is no match. A later WHERE on those columns drops the row.

When a report looks short, the first question is "did a NULL fail a comparison?". Select the suspect column, count the nulls with COUNT(*) - COUNT(column), and then decide whether those rows should have been excluded or should have been coalesced to a default.

Get WHERE, joins, grouping, and NULL solid before the rest of the dialect. Those four decide whether the number on the page is the number you asked for.