From Relations to SQL: The Model Behind a Query

A SQL table looks like a spreadsheet, but the resemblance is misleading. The relational model gives a table meaning through its schema, keys, domains, and constraints—not through the row order shown by a client.

Schema and instance

A schema describes the allowed shape of the data. An instance is the set of rows that exists at one moment.

CREATE TABLE enrollment (
  student_id bigint NOT NULL,
  course_id  text   NOT NULL,
  grade      integer,
  PRIMARY KEY (student_id, course_id),
  CHECK (grade BETWEEN 0 AND 100)
);

The column types, composite primary key, and check constraint belong to the schema. The rows inserted today belong to the instance.

In plain terms: the schema is the rulebook; the instance is today’s game state. A row can have the right number of columns and still be illegal if it breaks a key or constraint.

A relation has no display order

In the mathematical model, a relation is a set of tuples. Sets have no order, so a query without ORDER BY makes no promise about which row appears first. An execution-plan change, a new index, or parallel execution can change the visible order without changing the result.

SQL also differs from pure relational algebra because SQL normally uses bag semantics: duplicates are preserved.

SELECT course_id FROM enrollment;          -- duplicates allowed
SELECT DISTINCT course_id FROM enrollment; -- duplicate removal costs work

DISTINCT may require sorting or hashing. Use it when duplicates are wrong, not as a patch for an unexplained join.

The logical order of a SELECT

The clauses are written in one order but reasoned about in another:

FROM / JOIN → WHERE → GROUP BY → HAVING
            → SELECT → DISTINCT → ORDER BY → LIMIT

This explains three common surprises:

A SELECT alias is normally unavailable in WHERE: the alias does not exist yet.
Aggregate conditions belong in HAVING: groups do not exist during WHERE.
ORDER BY can use a SELECT alias because ordering happens later.

The optimizer may physically reorder joins or push filters downward, but only when the rewritten plan preserves this logical result.

NULL means unknown, not empty

NULL marks missing or inapplicable information. Comparisons with it produce UNKNOWN, the third value in SQL logic.

SELECT * FROM enrollment WHERE grade = NULL;  -- never TRUE
SELECT * FROM enrollment WHERE grade IS NULL; -- correct

WHERE keeps only rows whose predicate is TRUE; both FALSE and UNKNOWN are removed.

In plain terms: NULL is not a mysterious value that equals another NULL. It is the absence of an answer, so SQL cannot honestly say two unknown answers are equal.

A small verification table

INSERT INTO enrollment VALUES
  (1, 'CS411', 95),
  (2, 'CS411', NULL),
  (3, 'CS425', 88),
  (4, 'CS425', 88);

SELECT course_id, COUNT(*) AS students, COUNT(grade) AS graded
FROM enrollment
GROUP BY course_id
ORDER BY course_id;

COUNT(*) counts rows. COUNT(grade) ignores NULL. The difference is not syntax trivia: the two expressions answer different questions.

Review card

Schema is structure and rules; instance is current data.
Keys and constraints define legal states.
No ORDER BY means no ordering guarantee.
SQL preserves duplicates unless asked not to.
Reason about SELECT in logical, not written, order.
NULL introduces UNKNOWN; test it with IS NULL.

SQL table 看起來像試算表，但這個外觀很容易誤導。關聯模型不是靠畫面上的列順序定義資料，而是靠 schema、key、domain 與 constraint 定義什麼狀態合法。

Schema 與 instance

Schema 描述資料允許的結構；instance 是某一個時間點實際存在的 rows。

CREATE TABLE enrollment (
  student_id bigint NOT NULL,
  course_id  text   NOT NULL,
  grade      integer,
  PRIMARY KEY (student_id, course_id),
  CHECK (grade BETWEEN 0 AND 100)
);

欄位型別、複合主鍵和檢查條件屬於 schema；今天插入的資料則是 instance。

白話來說： Schema 是規則書，instance 是今天的比賽狀態。一筆資料即使欄位數量正確，只要違反 key 或 constraint，仍然不是合法資料。

Relation 沒有顯示順序

數學上的 relation 是 tuple 的集合，而集合沒有順序。因此沒有 ORDER BY 的查詢，不保證哪一列先出現。新增索引、換 execution plan 或啟用平行查詢，都可能改變顯示順序，卻沒有改變查詢語意。

SQL 和純關聯代數還有一個差異：SQL 預設採用 bag semantics，會保留重複值。

SELECT course_id FROM enrollment;          -- 允許重複
SELECT DISTINCT course_id FROM enrollment; -- 去重需要額外工作

DISTINCT 可能需要排序或 hash。只有當重複本身是錯的才使用它，不要拿它掩蓋沒弄懂的 join。

SELECT 的邏輯處理順序

SQL 寫下來的順序，不等於推理結果時的順序：

FROM / JOIN → WHERE → GROUP BY → HAVING
            → SELECT → DISTINCT → ORDER BY → LIMIT

這能解釋三個常見問題：

WHERE 通常不能使用 SELECT alias，因為 alias 當時還不存在。
聚合條件要放 HAVING，因為執行 WHERE 時還沒有 group。
ORDER BY 可以使用 SELECT alias，因為排序發生得比較晚。

Optimizer 可以在實體計畫裡調整 join 順序或下推 filter，但前提是結果仍符合這套邏輯順序。

NULL 是未知，不是空字串

NULL 表示資訊缺失或不適用。任何一般比較只要碰到它，結果通常是第三種邏輯值 UNKNOWN。

SELECT * FROM enrollment WHERE grade = NULL;  -- 不會得到 TRUE
SELECT * FROM enrollment WHERE grade IS NULL; -- 正確

WHERE 只保留 predicate 為 TRUE 的 rows；FALSE 和 UNKNOWN 都會被移除。

白話來說： NULL 不是一個可以和另一個 NULL 比較的神秘值，而是「沒有答案」。兩個都不知道，SQL 就不能說它們相等。

用四筆資料驗證

INSERT INTO enrollment VALUES
  (1, 'CS411', 95),
  (2, 'CS411', NULL),
  (3, 'CS425', 88),
  (4, 'CS425', 88);

SELECT course_id, COUNT(*) AS students, COUNT(grade) AS graded
FROM enrollment
GROUP BY course_id
ORDER BY course_id;

COUNT(*) 計算 rows；COUNT(grade) 忽略 NULL。這不是語法細節，而是兩個不同問題。

複習卡

Schema 是結構與規則；instance 是目前資料。
Key 與 constraint 定義合法狀態。
沒有 ORDER BY 就沒有順序保證。
SQL 預設保留重複資料。
用邏輯順序推理 SELECT，不要照書寫順序猜。
NULL 會產生 UNKNOWN；使用 IS NULL 判斷。