The semantics nobody teaches

-- written -- evaluated
SELECT   ...                     1. FROM + JOIN build the working set
FROM     ...                     2. WHERE filter individual rows
WHERE    ...                     3. GROUP BY collapse into groups
GROUP BY ...                     4. HAVING filter groups
HAVING   ...                     5. window funcs   computed over the result
ORDER BY ...                     6. SELECT project, assign aliases
LIMIT    ...                     7. DISTINCT
8. ORDER BY aliases now exist
9. LIMIT / OFFSET

-- alias in WHERE: fails. WHERE is step 2, alias is step 6.
SELECT price * 1.2 AS gross FROM items WHERE gross > 100;   -- ERROR

-- alias in ORDER BY: works. ORDER BY is step 8.
SELECT price * 1.2 AS gross FROM items ORDER BY gross;      -- OK

-- aggregate in WHERE: fails. groups do not exist yet.
SELECT cat, COUNT(*) FROM items
 WHERE COUNT(*) > 5 GROUP BY cat;                          -- ERROR

-- HAVING is the filter that runs AFTER grouping.
SELECT cat, COUNT(*) FROM items
 GROUP BY cat HAVING COUNT(*) > 5;                        -- OK

-- window function in WHERE: fails. windows are step 5.
SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM items WHERE rn <= 10;                                -- ERROR

-- wrap it so the window has already been computed.
SELECT * FROM (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM items
) t WHERE rn <= 10;                                       -- OK

CREATE TABLE t (x INT);
INSERT INTO t VALUES (5), (7), (NULL);

SELECT COUNT(*) FROM t;                -- 3
SELECT COUNT(*) FROM t WHERE x = 5;      -- 1
SELECT COUNT(*) FROM t WHERE x <> 5;     -- 1, not 2!
--   1 + 1 = 2, but the table has 3 rows.
--   the NULL row satisfies NEITHER predicate.

SELECT COUNT(*) FROM t WHERE x = 5 OR x IS NULL;   -- 2

-- and the three-valued result itself:
SELECT 5 = NULL       AS eq,      -- NULL
       5 <> NULL      AS ne,      -- NULL
       NOT(5 = NULL)  AS n,       -- NULL
       (NULL AND FALSE) AS af,     -- 0   ← FALSE wins
       (NULL OR TRUE)   AS ot;     -- 1   ← TRUE wins

COALESCE(a, b, c)        -- first non-NULL. standard, variadic.
NULLIF(a, b)             -- NULL if a = b, else a. good for /0 guards.
IFNULL(a, b)             -- MySQL, two-arg COALESCE
a IS NOT DISTINCT FROM b  -- NULL-safe equality: NULL = NULL is TRUE
a <=> b                 -- MySQL's spelling of the same thing

CREATE TABLE s (v INT);
INSERT INTO s VALUES (10), (20), (NULL), (NULL);

SELECT COUNT(*)      AS rows_,      -- 4
       COUNT(v)      AS non_null,   -- 2
       SUM(v)        AS total,      -- 30
       AVG(v)        AS avg_,       -- 15   (30/2, NOT 30/4)
       SUM(v)/COUNT(*) AS avg_wrong  -- 7.5  ← what people assume
FROM s;

-- GROUP BY puts all NULLs in ONE group...
SELECT v, COUNT(*) FROM s GROUP BY v;     -- 3 rows: 10, 20, NULL(2)

-- ...but = never matches them.
SELECT COUNT(*) FROM s a JOIN s b ON a.v = b.v;   -- 2, not 6

-- NULL-safe equality fixes that:
SELECT COUNT(*) FROM s a JOIN s b ON a.v <=> b.v;   -- 6  (MySQL)
-- Postgres: ON a.v IS NOT DISTINCT FROM b.v

-- UNIQUE permits many NULLs:
CREATE TABLE u (e VARCHAR(50) UNIQUE);
INSERT INTO u VALUES (NULL), (NULL), (NULL);   -- all succeed

-- Postgres: index only the rows you query
CREATE INDEX idx_active ON orders (customer_id)
  WHERE cancelled_at IS NULL;

-- MySQL has no partial indexes; the equivalent is a
-- generated column plus an index on it.

-- implementation-defined by default:
--   MySQL     NULLs FIRST on ASC, LAST on DESC
--   Postgres  NULLs LAST  on ASC, FIRST on DESC   ← opposite
-- portable and explicit (Postgres, Oracle, SQLite):
ORDER BY shipped_at DESC NULLS LAST;

-- MySQL has no NULLS LAST; sort on a computed flag first:
ORDER BY (shipped_at IS NULL), shipped_at DESC;
--   the boolean sorts 0 before 1, putting non-NULLs first.

-- these are equivalent
(a, b) > (3, 7)
a > 3 OR (a = 3 AND b > 7)

-- also valid
(a, b) = (3, 7)
(a, b) IN ((1,2), (3,4))

-- SLOW: the engine generates and throws away 100,000 rows
SELECT id, created_at, title FROM posts
 ORDER BY created_at DESC, id DESC
 LIMIT 20 OFFSET 100000;

-- FAST: seek straight to the position, constant time per page.
-- carry the last row's (created_at, id) from the previous page.
SELECT id, created_at, title FROM posts
 WHERE (created_at, id) < ('2026-09-21 10:00:00', 48221)
 ORDER BY created_at DESC, id DESC
 LIMIT 20;

-- the index that makes it a single seek + 20-row walk:
CREATE INDEX idx_page ON posts (created_at DESC, id DESC);

-- MySQL 5.7 could not optimise row-value comparisons well;
-- 8.0 handles them properly. Verify with EXPLAIN that you
-- get a range scan rather than a full scan.