Relational Foundations
SQL vs. the Relational Model
SQL is relationally inspired but is not a direct notation for the pure relational model: it permits duplicates, nulls, ordered presentation, and other constructs outside that model. Treating the model as a semantic compass while understanding SQL’s defined behavior produces more accurate and maintainable queries.
- A SQL base table with a key, non-null attributes, and no duplicate rows can closely represent a relation, but SQL does not require every table or result to do so.
- SQL defaults to bags; relations are sets.
- SQL nulls introduce
UNKNOWNand three-valued predicates; the classical relational model uses ordinary values and two-valued logic. - SQL columns have a written order and query output can be sorted, whereas relation attributes and tuples have no intrinsic order.
- SQL syntax mixes logical expression with practical language features.
- Relational reasoning remains valuable because it clarifies identity, predicates, closure, and equivalent transformations beneath product-specific syntax.
Principle and practice
| Issue | Relational position | Pragmatic SQL behavior |
|---|---|---|
| Duplicates | A relation is a set; duplicate tuples do not exist | SELECT uses bags by default; DISTINCT and set operators can deduplicate |
| Missing information | Date argues that null markers and three-valued logic should be avoided | SQL supports NULL; comparisons can yield UNKNOWN, and schemas commonly permit it |
| Ordering | No intrinsic tuple or attribute order | Columns have a presentation order; ORDER BY creates an ordered query result |
| Identity | Every relation has at least one candidate key, including possible compound or empty keys | SQL tables may be declared without any key |
| Operator result | Relational closure always yields a relation | Some SQL results may contain duplicate rows, anonymous expressions, or implementation-specific types |
For example, SELECT department_id FROM employee is a valid SQL query whose repeated department IDs can be useful when a later aggregate counts employees. It is not relational projection as defined by set algebra. Adding DISTINCT makes the result set-like; leaving it out is equally legitimate when multiplicity is part of the intended calculation.