Writing SQL from Scratch: The Query Keywords You Actually Need

Database

SQL

PostgreSQL

5 days ago2309 words·12 min read

What You'll Get Out of This

Understand what SQL is, and why writing it feels so different from writing JavaScript;
Master the complete skeleton of a SELECT statement, and learn the keyword that "does what" by heart;
Be able to read and write the queries behind 90% of daily work — filtering, sorting, grouping, and joining tables.

Before We Start

Read the previous article, so "table", "primary key", and "declarative" are fresh:

A PostgreSQL you can run (a Docker postgres image is plenty).

A Scenario to Start From

In the last article, we turned a JSON file into a table. Now your book list lives in a real table like this:

sql

CREATE TABLE books (
  id     BIGSERIAL PRIMARY KEY,
  title  VARCHAR(200),
  author VARCHAR(100),
  price  INT,
  read   BOOLEAN
);

Data is in, and a whole new world of questions opens up: which books cost less than 50? Who's the author with the most books? What's the average price? Which books in my list haven't been read yet?

To ask these questions, you need SQL. And that's exactly what many beginners trip on: they understand what a table is, but have no idea how to write a query. So let's build that skill from zero.

What SQL Is: Declarative, Not Procedural

Remember the key idea from the last article: in a file, you describe how to find things; in a database, you describe what you want. SQL is the language for the second way.

// JavaScript: you decide every step for the computer
const cheap = books.filter((b) => b.price < 50).map((b) => b.title);

sql

-- SQL: you only describe the result you want
SELECT title FROM books WHERE price < 50;

You don't tell the database how to scan, how to loop, or how to compare. You say what you want, and the database figures out how on its own (that's the optimizer's job, the star of the next article).

The Four Families of SQL

SQL statements fall into four families. You don't need to memorize all of them — just know they exist:

Family	Full name	What it does	Keywords
DQL	Data Query	Read data (90% of your work)	`SELECT`
DML	Data Manipulation	Insert / update / delete	`INSERT` `UPDATE` `DELETE`
DDL	Data Definition	Create / alter tables	`CREATE` `ALTER` `DROP`
DCL	Data Control	Permissions	`GRANT` `REVOKE`

The one you'll use every day is DQL — queries. So the rest of this article is all about SELECT.

The Skeleton of SELECT: Written Order vs. Execution Order

A SELECT has a fixed skeleton. Learn the written order first:

sql

SELECT  column         -- ① which columns
FROM    table          -- ② which table
WHERE   condition      -- ③ filter rows first
GROUP BY grouping      -- ④ group by some column
HAVING   group_cond    -- ⑤ filter groups after grouping
ORDER BY sort_column   -- ⑥ sort
LIMIT   n              -- ⑦ keep only the first n rows

Here's the trap that confuses everyone: the database does not execute in this written order. It goes roughly: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Why does this matter? Because WHERE filters rows before grouping, while HAVING filters groups after grouping. Keep that difference in mind — it explains most of the confusion around these two.

Walking Through the Keywords One by One

SELECT / FROM / AS — What to Fetch

sql

SELECT id, title AS book_title FROM books;
-- AS gives a column a nicer name; the keyword can be omitted
SELECT * FROM books;   -- * means "all columns" (fine in practice, but naming them is clearer)

WHERE — Filter Rows by Condition

sql

SELECT * FROM books WHERE price < 50;
SELECT * FROM books WHERE price BETWEEN 30 AND 60;    -- range
SELECT * FROM books WHERE title LIKE 'Data%';         -- fuzzy match; % matches anything
SELECT * FROM books WHERE read = true AND price < 50; -- combine with AND / OR / NOT
SELECT * FROM books WHERE id IN (1, 3, 5);            -- value in a set

ORDER BY / LIMIT — Sort and Cap

sql

SELECT * FROM books ORDER BY price DESC;   -- descending; ASC is the default
SELECT * FROM books ORDER BY price DESC LIMIT 10;  -- the 10 most expensive

Aggregate Functions — This Is Where "Statistics" Begins

Aggregate functions compute over a group of rows:

sql

SELECT COUNT(*) FROM books;   -- how many books
SELECT AVG(price) FROM books; -- average price
SELECT SUM(price) FROM books; -- total price
SELECT MAX(price), MIN(price) FROM books;

GROUP BY / HAVING — Grouped Statistics

This is the part that most often makes SQL feel hard. Skip the theory and look at some real data. Say books holds these rows:

txt

id  | title                | author            | price
----|----------------------|-------------------|------
1   | Database Internals   | Alex Petrov       | 79
2   | DDIA                 | Martin Kleppmann  | 118
3   | SQL Primer           | You               | 30
4   | PostgreSQL Deep Dive | Alex Petrov       | 89

Without GROUP BY, an aggregate works on "the whole table as one pile":

sql

SELECT COUNT(*) FROM books;   -- 4, counting every row as one group

Add GROUP BY and everything changes. GROUP BY author does two things:

Step 1: split into buckets — rows with the same author go into the same pile:

txt

Alex Petrov       → bucket ① (rows 1, 4)
Martin Kleppmann  → bucket ② (row 2)
You               → bucket ③ (row 3)

Step 2: run the aggregate per bucket — COUNT(*) counts each bucket separately:

txt

bucket ① (2 rows) → 2
bucket ② (1 row)  → 1
bucket ③ (1 row)  → 1

The result is "one row per author, with their book count next to it":

sql

SELECT author, COUNT(*) FROM books GROUP BY author;
-- Alex Petrov       | 2
-- Martin Kleppmann  | 1
-- You               | 1

So GROUP BY author really means: merge rows that share the same author into one group, and output one row per group. It looks like deduplication because each author shows up only once — but the goal isn't dedup, it's letting you compute statistics per group.

Here's the intuition that keeps everything straight: once GROUP BY appears, your SELECT scope shrinks from "the whole table" to "each group". Aggregates (COUNT/AVG/SUM/MAX/MIN) compute inside each group separately, and the SELECT can usually only contain the grouping column plus aggregate functions — because each group emits one row, and the other columns may hold several values, so the database can't know which one to show.

HAVING filters the groups after they've been computed:

sql

-- Only authors with more than 3 books
SELECT author, COUNT(*) AS cnt
FROM books
GROUP BY author
HAVING COUNT(*) > 3;

Think of GROUP BY as "split into buckets, count each bucket on its own", and HAVING as "after counting, keep only the buckets that qualify."

The critical distinction, once more: WHERE filters rows before grouping (say, drop expensive books, then group); HAVING filters groups after grouping. So conditions that involve aggregate functions must go in HAVING.

JOIN — Stitching Tables Together

Article 95 introduced foreign keys — tables connect through shared values. JOIN is how you put those tables back together in a query. At its heart it produces a wider virtual table — but how the rows line up is the real story.

Start with two tables, each carrying one "orphan":

txt

books                                  categories
id | title                | category_id  id | name
---|----------------------|------------  ---|------
1  | Database Internals   | 1            1  | Database
2  | JavaScript Advanced  | 2            2  | Frontend
3  | Computer Networks    | 1            3  | Algorithms
4  | Uncategorized Book   | NULL

Book 4 has category_id = NULL (no category); category 3 "Algorithms" has no books.

sql

-- Each book + its category name
SELECT books.title, categories.name
FROM books
JOIN categories ON books.category_id = categories.id;

JOIN ... ON does two things:

Step 1: cartesian product (exhaustive pairing) — every book pairs with every category:

txt

books 4 rows × categories 3 rows = 12 combinations
Database Internals  + Database / Frontend / Algorithms
JavaScript Advanced + Database / Frontend / Algorithms
Computer Networks   + Database / Frontend / Algorithms
Uncategorized Book  + Database / Frontend / Algorithms

Step 2: filter by the ON condition — keep only rows where books.category_id = categories.id:

txt

title                | name
---------------------|------
Database Internals   | Database      ← category_id(1) = id(1) ✓
JavaScript Advanced  | Frontend      ← category_id(2) = id(2) ✓
Computer Networks    | Database      ← category_id(1) = id(1) ✓
Uncategorized Book   | ?             ← NULL never equals anything ✗

The final result (default INNER JOIN):

txt

title                | name
---------------------|------
Database Internals   | Database
JavaScript Advanced  | Frontend
Computer Networks    | Database

Notice how both orphans end up: book 4 with category_id = NULL disappears, and "Algorithms" never shows up either — because INNER JOIN keeps only rows that match on both sides.

Swap JOIN for LEFT JOIN and the ending changes:

sql

SELECT books.title, categories.name
FROM books
LEFT JOIN categories ON books.category_id = categories.id;

Same process, one difference in the last step: all left-table (books) rows are kept, and unmatched right columns fill with NULL:

txt

title                | name
---------------------|------
Database Internals   | Database
JavaScript Advanced  | Frontend
Computer Networks    | Database
Uncategorized Book   | NULL      ← the left row survives, nothing to match

INNER JOIN means "only rows that exist on both sides"; LEFT JOIN means "the left side is the source of truth." Keep this in mind: a JOIN first builds a cartesian product, then picks the matches by ON — the result is a virtual wider table (left columns + right columns) that exists only during the query. Nothing is written to disk or changed in the original tables.

INSERT / UPDATE / DELETE — Changing Data

sql

INSERT INTO books (title, price) VALUES ('Database Internals', 79);
UPDATE books SET price = 59 WHERE title = 'Database Internals';
DELETE FROM books WHERE id = 5;

One rule to tattoo on your brain: always write the WHERE for UPDATE and DELETE. Without it, the statement applies to the entire table — the classic beginner's disaster.

How to Read a Foreign Query

When you meet an unfamiliar SQL statement, walk it through in your head in this order:

FROM — which table(s)?
WHERE — which rows get thrown away first?
Is there a GROUP BY? If yes, the SELECT can usually only contain the grouping column plus aggregate functions.
HAVING — which groups get thrown away next?
Finally, look at what columns are selected, how they're sorted, and how many rows are kept.

This mental model will carry you through every query you'll ever read.

Hands-On Practice

Spin up the PostgreSQL and play along:

bash

docker run --name sql-primer -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 postgres:16
docker exec -it sql-primer psql -U postgres

Exercise 1: Create and Fill

sql

CREATE TABLE books (
  id     BIGSERIAL PRIMARY KEY,
  title  VARCHAR(200),
  author VARCHAR(100),
  price  INT,
  read   BOOLEAN
);

INSERT INTO books (title, author, price, read) VALUES
  ('Database Internals', 'Alex Petrov', 79, false),
  ('DDIA', 'Martin Kleppmann', 118, true),
  ('SQL Primer', 'You', 30, true);

Exercise 2: Query, from Easy to Hard

sql

SELECT * FROM books;
SELECT title FROM books WHERE price < 80 ORDER BY price;
SELECT author, COUNT(*) FROM books GROUP BY author;

Now modify them: add a HAVING condition, add a LIKE, try BETWEEN. Then make the "forgot the WHERE" mistake deliberately on a copy — and watch how fast the whole table changes. It's the cheapest lesson you'll ever get.

Exercise 3 (Challenge): Join Two Tables

Create a categories table, give books a category_id column, and write a query that returns each book together with its category name using JOIN. Now change JOIN to LEFT JOIN and add one book with category_id = NULL — watch which row survives and which one disappears. Compare the two result sets.

Summary and Keywords

SQL is declarative: you describe what you want, the database decides how;
A SELECT has a fixed skeleton: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT — but it executes in a different order;
WHERE filters rows before grouping; HAVING filters groups after grouping;
GROUP BY splits rows into buckets by the grouping column, then aggregates run per bucket — your SELECT scope shrinks from "the whole table" to "each group";
Aggregate functions (COUNT/AVG/SUM/MAX/MIN) always work together with GROUP BY;
JOIN ... ON builds a cartesian product first, then filters by ON — producing a wider virtual table; INNER keeps only matching rows, LEFT keeps every left row;
Always write the WHERE for UPDATE and DELETE.

Keywords: SQL、DQL、SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT、JOIN、cartesian product、aggregate function、INSERT、UPDATE、DELETE、declarative

What's Next

Now that you can write and read a query, you might wonder: what actually happens inside the database after you press Enter? Your SQL is just text — the parser turns it into a structure, the optimizer picks a strategy, the executor pulls out the rows. That entire journey is exactly what we walk through next:

You'll meet the execution plan and EXPLAIN for the first time — and start to see why the database can "optimize" your query.