--- title: "06 — SQL Fundamentals: SELECT, Filter, Modify" uid: sql-fundamentals tags: ["sql", "roadmap:postgresql-dba", "insert", "postgresql", "select", "delete", "copy", "where", "update"] excerpt: "SQL splits into DDL (shape the tables) and DML (move the data) — and every query follows a fixed logical evaluation order that isn't the order you write it in." date: 2026-08-13T03:27:52+0000 source: https://www.aveshina.my.id/en/blog/sql-fundamentals --- 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. ```figure ``` ## 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](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/](https://www.sqltutorial.org/) [3] PostgreSQL Global Development Group, "CREATE TABLE," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/sql-createtable.html](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](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](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/](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](https://www.postgresql.org/docs/current/sql-copy.html) ```quiz Q: In what logical order does PostgreSQL evaluate a SELECT query? - SELECT → FROM → WHERE → GROUP BY → ORDER BY (the order you write it) - FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT correct: 1 explain: SQL is evaluated FROM-first, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. This differs from the written order and explains why SELECT aliases aren't visible to WHERE. Q: Why does HAVING exist as a separate clause from WHERE? - It's an alias for WHERE - It filters groups after GROUP BY runs, so it can reference aggregates that WHERE cannot see correct: 1 explain: WHERE filters rows before grouping; HAVING filters the grouped result, so it can use aggregates like COUNT(*) or SUM(x). Q: Why does WHERE status != 'paid' silently exclude rows where status is NULL? - Because NULL != 'paid' evaluates to unknown, and WHERE keeps only rows that are true - Because NULL values are always deleted automatically correct: 0 explain: Comparisons with NULL yield unknown, not true. WHERE keeps only true rows, so NULLs are excluded. Use IS DISTINCT FROM for NULL-safe inequality. Q: What is the fastest way to load a million rows into a table? - One million individual INSERT statements - The COPY command streaming directly from a file correct: 1 explain: COPY streams data between file and table, bypassing per-row overhead. It's typically one to two orders of magnitude faster than looping INSERTs. Q: Which of these is DDL, not DML? - SELECT - INSERT - CREATE TABLE correct: 2 explain: DDL shapes the schema (CREATE/ALTER/DROP TABLE). DML moves data (SELECT/INSERT/UPDATE/DELETE). Postgres makes DDL transactional, so it can run inside BEGIN/ROLLBACK. ```