Relational model and query semantics

Why it matters

SQL is declarative: you describe the result and the database chooses how to compute it. That makes the semantics the contract. Syntax errors fail loudly, so the SQL bugs that survive testing tend to be semantic ones that return plausible but wrong results: a join that multiplies rows and inflates a SUM, a LEFT JOIN that silently becomes an inner join, a NOT IN that returns nothing because of a single NULL. The same semantics decide which rewrites an optimizer may apply, so this topic is also the base for query optimization.

Core ideas

Relations and bags. In Codd's model a relation is a set of tuples. SQL tables and query results are bags: duplicates survive unless a key, DISTINCT or a set operator without ALL removes them. Projection does not deduplicate, UNION removes duplicates while UNION ALL keeps them, and a join emits one row per matching pair. If an order has three line items and two payments, joining all three tables yields six rows for that order, and summing payments triple-counts them. Aggregate each child table before joining, or join only along key-preserving paths.

Logical evaluation order. Conceptually a query runs FROM (with joins), then WHERE, GROUP BY, HAVING, SELECT (expressions and window functions), DISTINCT, ORDER BY and LIMIT. This explains why WHERE cannot use an aggregate, why HAVING filters groups and why a SELECT alias is not visible in WHERE. The physical plan may differ, as long as the result is the same.

Three-valued logic. A comparison with NULL yields UNKNOWN, and WHERE and HAVING keep only rows whose condition is TRUE. Consequences:

• x = NULL is never true; use IS NULL, or IS NOT DISTINCT FROM for null-safe equality.
• x NOT IN (SELECT y ...) is UNKNOWN for every x once the subquery yields a NULL, so the query returns nothing; NOT EXISTS has no such trap.
• Aggregates skip NULLs: COUNT(col) counts non-null values, COUNT(*) counts rows, AVG ignores NULLs instead of treating them as zero, and SUM over zero rows is NULL.
• GROUP BY and DISTINCT put all NULLs into one group, although NULL = NULL is not true. A UNIQUE constraint normally accepts several NULLs; PostgreSQL lets you opt out with NULLS NOT DISTINCT.

Outer joins. A LEFT JOIN keeps unmatched left rows padded with NULLs. A condition on the right table placed in WHERE discards exactly those padded rows; put it in the ON clause if unmatched rows must survive.

Keys and constraints. A candidate key is a minimal set of attributes that identifies a row; one becomes the primary key. A foreign key requires each referencing value to exist in the referenced key (or be NULL), with actions such as RESTRICT, CASCADE or SET NULL. NOT NULL, UNIQUE and CHECK are enforced by the database on every write, which is more reliable than checks scattered across services. A CHECK constraint sees one row at a time, so rules that span rows need something else: UNIQUE or exclusion constraints when the rule is uniqueness or non-overlap, and triggers or serializable transactions for rules such as "at least one doctor on call per shift".

Normalization. A functional dependency X → Y says equal X values imply equal Y values. When a non-key attribute depends on part of a key or on another non-key attribute, the same fact is stored repeatedly and updates can leave copies inconsistent. BCNF requires the left side of every non-trivial dependency to be a superkey; 3NF relaxes this slightly so that a dependency-preserving decomposition always exists. In R(student, course, instructor) where each instructor teaches one course, instructor → course violates BCNF. Practice: store each fact once, then denormalize deliberately (materialized views, cached counters, wide analytical tables) and say how the copies stay correct.

Common misconceptions

• "SQL tables are sets." They are bags; joins that fan out make counts and sums wrong.
• "NULL means zero or empty." It means unknown or missing; arithmetic with NULL yields NULL. Use COALESCE to substitute explicitly.
• "NOT IN and NOT EXISTS are interchangeable." They differ as soon as the subquery can produce a NULL.
• "A LEFT JOIN always keeps every left row." Only if no later WHERE condition rejects the NULL-padded rows.
• "Normalization is about performance." It is about avoiding anomalies; denormalization is a separate, explicit tradeoff.
• "ORDER BY in a subquery or view fixes the output order." Only the outermost ORDER BY defines the order of the final result.

How to approach it

Rebuild the rules with tiny tables you can evaluate by hand: three rows containing a duplicate and a NULL are enough to test everything above. Write the expected result before running each query. In interviews, state assumptions about uniqueness and NULLs aloud, check for fan-out before aggregating, and prefer NOT EXISTS for anti-joins. For schema questions, start from entities and keys, list the dependencies you believe hold, and say which constraints the database will enforce for you. Database-research interviews may go further into algebraic equivalences, for example why outer joins cannot always be reordered freely.