SQL Reference ยท Dialects and Concepts
The relational model
The relational model behind SQL: relations, tuples, attributes, domains, primary and foreign keys, relational algebra operators and how SQL differs from the theory.
The relational model is the theory SQL is built on. Edgar F. Codd proposed it in 1970 at IBM: store data as relations (tables) of facts, and describe what you want with a small set of operations rather than by following pointers. The database decides how to find the data.
SQL borrows the model's ideas but bends them in places. Knowing the original vocabulary makes SQL's behavior easier to predict, especially for keys, joins and duplicates.
The vocabulary
| Theory | SQL | Meaning |
|---|---|---|
| Relation | Table | A set of facts of the same shape |
| Tuple | Row | One fact |
| Attribute | Column | A named property with a domain |
| Domain | Data type (plus constraints) | The allowed values of an attribute |
| Degree, cardinality | Column count, row count | The size of the relation |
| Candidate key | UNIQUE / PRIMARY KEY |
A minimal set of attributes that identifies a tuple |
| Foreign key | FOREIGN KEY ... REFERENCES |
Attributes that point to a key of another relation |
| Null | NULL |
A marker for a missing or unknown value (not in the original theory) |
In a relation, tuples are unordered and have no duplicates, and each attribute holds one value per tuple. A SQL table may contain duplicate rows unless a key forbids them, and a query result is only ordered if you say ORDER BY.
Keys and integrity
- Primary key: the chosen identifier of each row. It must be unique and not null.
- Candidate keys: every column set that could serve; the rest are enforced with
UNIQUE. - Foreign key: a column whose values must exist in the key column of another table. This links tables and prevents orphans.
- Entity integrity: no part of a primary key is null. Referential integrity: every foreign key value matches a key or is null.
- Domain constraints: types,
NOT NULL,CHECK, defaults.
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Relational algebra and SQL
Codd's algebra has a handful of operators that take relations and return relations. Because the result is again a relation, they combine freely, which is what makes subqueries and views possible.
| Operator | Purpose | SQL |
|---|---|---|
| Selection (sigma) | Keep tuples that satisfy a condition | WHERE |
| Projection (pi) | Keep chosen attributes | the select list (and DISTINCT) |
| Union | Tuples in either relation | UNION |
| Difference | Tuples in the first but not the second | EXCEPT (MINUS) |
| Intersection | Tuples in both | INTERSECT |
| Cartesian product | All pairs of tuples | CROSS JOIN |
| Join | Product followed by selection | JOIN ... ON |
| Rename | Change relation or attribute names | AS |
| Division | Tuples related to all of another relation | no keyword; NOT EXISTS twice |
Press Run (or Ctrl + Enter) to see the result here.
Where SQL departs from the model
- Duplicates. SQL tables and results are bags, not sets.
UNION ALLand plainSELECTkeep duplicates;DISTINCTandUNIONremove them. - Order. Rows have no inherent order. Only
ORDER BYguarantees one. - Nulls and three-valued logic. A comparison with
NULLis neither true nor false but unknown, which makesNOT INand joins behave in ways pure relations would not. - Column order and duplicate column names exist in SQL results; attributes in the model are unordered and unique.
- Pointers are not part of the model. You relate rows by matching values, not by following links; keys and joins do that job.
- Updatable views and triggers go beyond the algebra.
Dialect differences
The model is the same everywhere; how strictly products honor it differs.
- SQLite allows any value in any column unless the table is
STRICT, and ignores foreign keys unless enabled. The other four systems enforce types and foreign keys by default. - Oracle treats the empty string as
NULL, which no other product does. - MySQL's older storage engines (MyISAM) accepted foreign key syntax but did not enforce it; InnoDB does.
INTERSECT/EXCEPTare missing from very old MySQL, and Oracle called differenceMINUSuntil 21c.
Usage notes
- Give every table a primary key. Without one, you cannot reliably identify or update a single row.
- Model one thing per table, and relate tables with foreign keys. See normalization.
- Think in sets. Instead of looping over rows, describe the whole result; the optimizer chooses the plan.
- Use constraints to make the database refuse bad data, instead of trusting every application to do the checking.
- Expect to design with ER diagrams: entities become tables, relationships become foreign keys or join tables.