Johnny Lin/ notes
← All writing2026.05.06 · CS 411 · Database Systems

From Relations to SQL: The Model Behind a Query

Relations, schemas, keys, bags, NULL, and SQL's logical processing order—one mental model for reasoning about query results.

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.