The Relational Model

The relational model, introduced by E. F. Codd, represents all data as relations — what SQL calls tables. Every relational database, regardless of vendor, is built on this same mathematical foundation, which is why SQL concepts transfer cleanly between PostgreSQL, MySQL, and other systems.

Understanding the relational model as "sets of tuples," rather than just "tables that look like spreadsheets," explains why SQL behaves the way it does: why row order is not guaranteed unless you explicitly sort, why duplicate rows are technically allowed but usually avoided, and why joining tables is a first-class, efficient operation rather than a workaround.

Why it matters

SQL works on whole sets of rows at once, not on one row at a time. People coming from programming languages reach for loops and are surprised that rows have no fixed order. Thinking in sets is the shift that makes SQL feel natural, and it is why one short statement can update a million rows.

Relations, Tuples, and Attributes

In relational theory, a "relation" is a table, a "tuple" is a row, and an "attribute" is a column. Formally, a relation is a set of tuples that all share the same attributes (columns) and types. This is why every row in a SQL table must have a value (or NULL) for every defined column — the structure is uniform by definition, unlike a loosely structured document format.

Set-Based Thinking

SQL is fundamentally set-based, not row-by-row like traditional procedural code. A query such as SELECT * FROM orders WHERE total > 100 describes the set of rows you want, not a loop that inspects rows one at a time. The database engine decides how to actually retrieve that set efficiently. This is a mental shift for programmers used to for-loops: instead of writing "for each row, check the condition," you describe the condition and let the engine figure out the best access path.

No Guaranteed Order Without ORDER BY

Because a relation is mathematically a set (or in SQL's practical implementation, a multiset that allows duplicates), there is no inherent row order. A query without an explicit ORDER BY clause may return rows in any order the engine finds convenient, and that order can change between runs, even without any data changes. Relying on unspecified order is one of the most common relational-model misunderstandings among beginners.

Example

SELECT name, department
FROM employees
WHERE department = 'Engineering'
ORDER BY name;

Returns 2 rows, ordered by name: ('Asha Rao', 'Engineering'), ('Meera Iyer', 'Engineering')

Exercise

The rows were inserted in the order Zoya, Asha, Ravi. A table has no guaranteed order, so write a query that returns the names in alphabetical order explicitly. Then raise every salary by 1000 with a single statement, and return the total of all salaries as total.

Expected output: name, Asha, Ravi, Zoya, total, 153000 (one per line)

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO staff (id, name, salary) VALUES (1, 'Zoya', 60000), (2, 'Asha', 50000), (3, 'Ravi', 40000);

-- TODO: the names in alphabetical order

-- TODO: one statement that raises every salary by 1000

-- TODO: the sum of all salaries as "total"

CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO staff (id, name, salary) VALUES (1, 'Zoya', 60000), (2, 'Asha', 50000), (3, 'Ravi', 40000);

SELECT name FROM staff ORDER BY name;

UPDATE staff SET salary = salary + 1000;

SELECT SUM(salary) AS total FROM staff;

Quick quiz

Interview questions