06 — SQL Fundamentals: SELECT, Filter, Modify

Syntax to memorize was how I treated SQL, and it never explained the why behind the rules. The framing that organized it: SQL divides into DDL, which shapes the tables, and DML, which moves the data inside them, and every query follows a fixed logical evaluation order that is not the order I write it in. [1][2] Once the evaluation order landed, the confusing parts — why I can't put an aggregate alias in WHERE, why HAVING exists separately from WHERE — resolved themselves.

DDL: shaping the table

Data Definition Language changes the shape of the database. The core verbs are CREATE TABLE, ALTER TABLE, and DROP TABLE [3]:

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE users ADD COLUMN display_name TEXT;
DROP TABLE users;

Each column gets a type and optional constraints; BIGSERIAL is shorthand for an auto-incrementing bigint primary key. DDL in Postgres is transactional — I can run CREATE TABLE inside a BEGIN/ROLLBACK, which is invaluable for testing migrations.

DML: reading and writing data

Data Manipulation Language is the four-verb core of everyday work [4]:

• SELECT — read rows. The query I write 95% of the time.
• INSERT — add rows.
• UPDATE — change existing rows (writes new versions under MVCC).
• DELETE — remove rows (flags them dead under MVCC).

INSERT INTO users (email) VALUES ('ave@example.com');
UPDATE users SET display_name = 'Ave' WHERE id = 1;
DELETE FROM users WHERE id = 1;

The evaluation order that clarifies everything

The single insight that unblocked me: SQL's logical evaluation order is not the order I type. A query is evaluated FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT [1]. This is why I can't reference a SELECT alias in WHERE — at the time WHERE runs, the SELECT list hasn't been computed yet.

Filtering: WHERE and the NULL trap

WHERE filters rows by a boolean condition [5]. The operators are the usual ones (=, !=, <, >), plus IN, BETWEEN, LIKE/ILIKE for patterns, and IS NULL/IS NOT NULL. The trap, covered in the relational-model notes, is that comparisons with NULL yield unknown, not false — so WHERE status != 'paid' silently drops rows where status is NULL. IS DISTINCT FROM is the NULL-safe inequality.

Postgres also offers a FILTER clause on aggregates, cleaner than a CASE for conditional counts:

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_count
FROM orders;

Grouping: GROUP BY and HAVING

GROUP BY buckets rows by the listed columns, collapsing each bucket into one output row via aggregates (COUNT, SUM, AVG, MAX, MIN) [6]. HAVING filters the buckets — it's WHERE for groups, and it exists precisely because WHERE runs before GROUP BY and can't see aggregates.

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

This counts orders per user and keeps only users with more than five. Moving the COUNT(*) > 5 condition into WHERE would fail, because at WHERE time the per-user count doesn't exist yet.

Bulk load and unload: COPY

For moving large volumes of data in or out, COPY is dramatically faster than row-by-row INSERT [7]:

COPY users (email, display_name) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);
COPY (SELECT * FROM users WHERE created_at > now() - interval '30 days') TO '/tmp/recent.csv' WITH CSV HEADER;

COPY streams directly between a file and the table, bypassing much of the per-row overhead. The server-side COPY requires the file to be readable by the Postgres process; the \copy variant in psql runs client-side and reads files the local user can see. For initial data loads and exports, this is the tool.

How I use this

The evaluation order is the habit. When a query won't compile or gives a weird result, I rewrite it in evaluation order in my head — what does FROM produce, what survives WHERE, what do the groups look like — and the bug usually surfaces. For any bulk insert, I reach for COPY (or its ORM equivalent) instead of looping INSERTs, because the speed difference is easily two orders of magnitude. And I keep DDL inside transactions when testing migrations, so a bad migration rolls back cleanly. The syntax is memorization; the evaluation order is the model.

References

[1] PostgreSQL Global Development Group, "The SELECT Statement," 2024. [Online]. Available: https://www.postgresql.org/docs/current/sql-select.html

[2] SQLTutorial.org, "SQL Tutorial — Essential SQL for the Beginners," 2024. [Online]. Available: https://www.sqltutorial.org/

[3] PostgreSQL Global Development Group, "CREATE TABLE," 2024. [Online]. Available: https://www.postgresql.org/docs/current/sql-createtable.html

[4] PostgreSQL Global Development Group, "Modifying Data," 2024. [Online]. Available: https://www.postgresql.org/docs/current/sql-insert.html

[5] Prisma, "How to filter query results in PostgreSQL," 2024. [Online]. Available: https://www.prisma.io/dataguide/postgresql/reading-and-querying-data/filtering-data

[6] PostgreSQLTutorial.com, "PostgreSQL GROUP BY," 2024. [Online]. Available: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-group-by/

[7] PostgreSQL Global Development Group, "COPY," 2024. [Online]. Available: https://www.postgresql.org/docs/current/sql-copy.html