The relational model, and your first queries

A table is a set of rows, each with the same named columns. That sounds obvious and it has two consequences people trip over immediately.

A set has no order. A table has no first row. If you do not write ORDER BY, the engine may return rows in any order it likes, and “any order it likes” is allowed to change between two runs of the same query — after an update, after a new index, after a version upgrade. Every pagination bug in the world starts here.

A column has one type and one meaning. status holds a status. It does not hold a status and sometimes a reason for the status. The moment one column means two things, every query about it grows an OR.

SQL is declarative: you describe the result you want, not how to get it. A component called the planner (PostgreSQL) or optimizer (MySQL) decides how to actually execute it. That gap is the whole reason lesson 8 exists — two queries that mean the same thing can differ by a factor of a thousand.

The schema for this course

Every lesson uses these four tables. Create them once now.

PostgreSQL:

CREATE TABLE customer (
  id          bigserial PRIMARY KEY,
  email       text        NOT NULL UNIQUE,
  full_name   text        NOT NULL,
  country     text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE product (
  id      bigserial PRIMARY KEY,
  sku     text    NOT NULL UNIQUE,
  name    text    NOT NULL,
  cents   integer NOT NULL CHECK (cents >= 0)
);

CREATE TABLE "order" (
  id           bigserial PRIMARY KEY,
  customer_id  bigint      NOT NULL REFERENCES customer(id),
  status       text        NOT NULL,
  placed_at    timestamptz NOT NULL,
  shipping_cents integer   NOT NULL DEFAULT 0,
  shipped_at   timestamptz,          -- NULL means not shipped yet
  coupon_code  text                  -- NULL means no coupon
);

CREATE TABLE order_item (
  order_id    bigint  NOT NULL REFERENCES "order"(id),
  product_id  bigint  NOT NULL REFERENCES product(id),
  quantity    integer NOT NULL CHECK (quantity > 0),
  unit_cents  integer NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

MySQL:

CREATE TABLE customer (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  full_name   VARCHAR(255) NOT NULL,
  country     VARCHAR(2)   NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product (
  id     BIGINT AUTO_INCREMENT PRIMARY KEY,
  sku    VARCHAR(64)  NOT NULL UNIQUE,
  name   VARCHAR(255) NOT NULL,
  cents  INT          NOT NULL CHECK (cents >= 0)
);

CREATE TABLE `order` (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_id  BIGINT      NOT NULL,
  status       VARCHAR(32) NOT NULL,
  placed_at    DATETIME    NOT NULL,
  shipping_cents INT       NOT NULL DEFAULT 0,
  shipped_at   DATETIME    NULL,
  coupon_code  VARCHAR(32) NULL,
  FOREIGN KEY (customer_id) REFERENCES customer(id)
);

CREATE TABLE order_item (
  order_id    BIGINT NOT NULL,
  product_id  BIGINT NOT NULL,
  quantity    INT    NOT NULL CHECK (quantity > 0),
  unit_cents  INT    NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES `order`(id),
  FOREIGN KEY (product_id) REFERENCES product(id)
);

Two things are already worth noticing. order is a reserved word, so it has to be quoted — "order" in PostgreSQL, `order` in MySQL. That alone is a good argument for not naming a table order; this course keeps the name precisely so the quoting rule stays in front of you.

And the timestamp types are not equivalent. PostgreSQL’s timestamptz stores an absolute instant and converts on the way in and out. MySQL’s DATETIME stores wall-clock text with no zone at all. That difference produces real bugs, and lesson 7 covers it properly.

SELECT: projection and selection

Two independent operations that beginners blur together.

SELECT full_name, country      -- projection: which columns
FROM customer
WHERE country = 'ID';          -- selection: which rows

SELECT * is fine at a prompt and a liability in code: it makes the result depend on the table’s current column list, so adding a column changes what your application receives.

Expressions are allowed, and this is where the first dialect split appears.

-- PostgreSQL
SELECT full_name, cents / 100.0 AS price, 'SKU-' || sku AS label FROM product;

-- MySQL
SELECT full_name, cents / 100.0 AS price, CONCAT('SKU-', sku) AS label FROM product;

In MySQL, || means OR by default, not concatenation. 'SKU-' || sku does not error — it evaluates both sides as booleans and returns 0 or 1. A query that returns the wrong thing silently is worse than one that fails, so use CONCAT.

WHERE

The comparison operators are the ones you expect: =, <> (also !=), <, >, <=, >=, plus BETWEEN, IN, LIKE.

SELECT * FROM "order"
WHERE status IN ('paid', 'shipped')
  AND placed_at >= '2026-01-01'
  AND coupon_code IS NULL;

Note IS NULL, not = NULL. That is not a style preference — = NULL is never true, and it is the subject of the whole next lesson.

BETWEEN is inclusive on both ends, which makes it the wrong tool for dates: placed_at BETWEEN '2026-01-01' AND '2026-01-31' misses everything that happened during the 31st after midnight. Use a half-open range instead:

WHERE placed_at >= '2026-01-01' AND placed_at < '2026-02-01'

Half-open ranges also tile perfectly — the end of one month is the start of the next, with no gap and no overlap to reason about.

Pattern matching, and case

LIKE uses % for any run of characters and _ for exactly one.

The defaults are opposite, which is the trap. In PostgreSQL LIKE 'a%' will not match Alice. In MySQL, with the usual utf8mb4_0900_ai_ci collation, it will — and so will =, meaning WHERE email = 'ALICE@EXAMPLE.COM' finds the row. Code that relies on either behaviour breaks when moved.

ORDER BY

SELECT id, status, shipped_at
FROM "order"
ORDER BY shipped_at DESC, id DESC;

The second key is not decoration. Sorting by a column with ties leaves the order within a tie unspecified, so a paginated list can show the same row on page 1 and page 2. Add a unique tiebreaker — usually the primary key — to every ORDER BY you paginate.

NULLs sort differently, and here the engines disagree again:

-- PostgreSQL
ORDER BY shipped_at DESC NULLS LAST;

-- MySQL: same intent
ORDER BY (shipped_at IS NULL), shipped_at DESC;

LIMIT and OFFSET

SELECT id, placed_at FROM "order" ORDER BY placed_at DESC, id DESC LIMIT 20 OFFSET 40;

Both engines accept this form. OFFSET is fine for page 3 and a bad idea for page 3000, because the engine must produce and discard every skipped row — OFFSET 60000 does 60,020 rows of work to return 20. Lesson 10 replaces it with keyset pagination.

DISTINCT

DISTINCT deduplicates whole result rows, not individual columns:

SELECT DISTINCT country FROM customer;              -- one row per country
SELECT DISTINCT country, status FROM "order" o
  JOIN customer c ON c.id = o.customer_id;          -- one row per pair

If you find yourself adding DISTINCT to fix duplicated rows after a join, the DISTINCT is usually a symptom: the join is multiplying rows and you actually wanted an aggregate or a semi-join. Lesson 4 covers that.

PostgreSQL also has DISTINCT ON (expr), which keeps the first row per group in ORDER BY order — genuinely useful and not portable:

-- Latest order per customer, PostgreSQL only
SELECT DISTINCT ON (customer_id) customer_id, id, placed_at
FROM "order"
ORDER BY customer_id, placed_at DESC;

The MySQL equivalent is a window function, which is lesson 6.

Identifier quoting and case folding

The practical rule: name everything in lower_snake_case and never quote anything. Then no dialect, no operating system and no future migration can reinterpret your names. This site learned the same lesson one layer up — its database is called site-db, and the hyphen means every statement that names it has to be quoted forever.

What to take away

• No ORDER BY means no order, and no guarantee it stays the same.
• IS NULL, never = NULL.
• Half-open date ranges, not BETWEEN.
• CONCAT in MySQL; || there means OR.
• Case sensitivity of LIKE and = is opposite between the two engines.
• lower_snake_case, unquoted, everywhere.