Where SQL came from

Edgar F. Codd, an IBM researcher, published A Relational Model of Data for Large Shared Data Banks in 1970. The problem he was solving is easy to underestimate now, because we have forgotten the alternative.

Databases of the era — IMS, CODASYL — required you to navigate. To find a customer's orders you followed pointers through a hierarchy or network, in code, in the order the database had physically stored them. Your program encoded the physical layout. Change the layout and every program broke.

Codd's proposal was physical data independence: describe data as mathematical relations, query it by describing what you want, and let the system decide how to retrieve it. The schema and the access path become separate concerns.

The model rests on three ideas: data is held in relations (sets of tuples over named, typed attributes), manipulated by a closed algebra (operations on relations return relations, so they compose), and accessed by value, never by position or pointer.

Codd's own query language, Alpha, was mathematical. In 1974 Donald Chamberlin and Raymond Boyce at IBM designed SEQUEL — Structured English Query Language — deliberately targeting users who were not mathematicians. The English-like syntax was a product decision about adoption.

It was renamed SQL because SEQUEL was a trademark of a British aircraft company. The pronunciation argument descends directly from that, and both are correct.

SEQUEL was implemented in System R, IBM's prototype, which contributed far more than a language. System R produced the first cost-based optimizer, the ARIES-lineage recovery ideas, and the isolation-level definitions still in use. Much of the MySQL module describes System R's intellectual descendants.

Know which release introduced what, because it tells you what you can rely on across engines.

Four forces, all of them structural rather than careless:

1. Vendors shipped before the standard existed. Oracle had DECODE and ROWNUM before CASE and LIMIT were standardized. Backward compatibility then froze them.
2. The standard leaves things implementation-defined. NULL sort order, identifier case sensitivity, and the exact behaviour of several functions are explicitly left open.
3. Engine architecture leaks through. MySQL has no merge join because InnoDB's storage model makes it less useful; Postgres has index access methods because it was designed for extensibility. The SQL you can write efficiently follows the engine beneath it.
4. Extensions are competitive advantage. JSONB, ON CONFLICT, SKIP LOCKED, arrays — vendors ship them to win, and the standard catches up later, sometimes with different syntax.

SQL is based on the relational model but departs from it in four ways that cause real confusion. Naming them explicitly is the best preparation for Part 1.

Codd's own objection

Codd disliked SQL, particularly NULL as implemented. His position was that a missing value and an inapplicable value are different things needing different markers. SQL collapses both into one NULL, which is why NULL = NULL is unknown rather than either true or false.

-- 1. bags, not sets
SELECT 1 UNION ALL SELECT 1;    -- two rows. not a set.
SELECT 1 UNION     SELECT 1;    -- one row. dedup costs a sort or hash.

-- 2. columns have positions
SELECT 2 AS a, 1 AS b ORDER BY 1;   -- orders by 'a' — by POSITION

-- 3. NULL and three-valued logic
SELECT NULL = NULL;              -- NULL, not 1
SELECT NULL <> NULL;             -- NULL, not 0
SELECT NULL IS NULL;             -- 1. the only test that works.

-- 4. row order is undefined without ORDER BY
--    this may LOOK ordered for years, then change with a new
--    index or a plan change. never rely on it.
SELECT id FROM some_table LIMIT 10;

Part 1 takes the third and fourth of those seriously — evaluation order and NULL — because together they explain most of the behaviour that surprises experienced engineers.