Data types and NULL: what a column is allowed to hold

Sorting mail into the right slot

Imagine a post office with pigeonholes labeled "letters," "postcards," and "packages." If someone tries to shove a package into the letters slot, it simply won't fit — the slot is shaped for something smaller. That physical constraint prevents a whole category of mistakes before they can even happen.

A column's data type is exactly this: a fixed shape that every value in that column must fit. If a column is declared INTEGER, the database physically will not let the text "banana" in — it rejects the insert outright. This is the database enforcing correctness before bad data ever gets stored, rather than your application having to check everything by hand, every time, everywhere it touches the data.

The core data types, and why each one exists

Every relational database has its own exact list of type names, but they all boil down to the same handful of ideas:

The math you must never skip: why money uses DECIMAL, not FLOAT

This is one of the most common real-world bugs in all of software, and it comes directly from how computers represent numbers in binary.

Floating-point numbers (FLOAT, DOUBLE) store values in binary fractions — powers of $2$ (like $\frac{1}{2}, \frac{1}{4}, \frac{1}{8}$) added together. The problem: many ordinary decimal fractions, like $0.1$, cannot be represented exactly in binary, the same way $\frac{1}{3}$ cannot be written exactly in decimal (it's $0.333\ldots$ forever).

Concretely, if you add $0.1 + 0.2$ using floating point, you do not get exactly $0.3$:

$0.1 + 0.2 = 0.30000000000000004\ldots \ne 0.3$

For a game's physics engine, that tiny error is irrelevant. For a bank account, it is a genuine bug — multiply small errors across millions of transactions and real money goes missing or appears from nowhere. DECIMAL(p, s) avoids this entirely by storing numbers as exact digit sequences instead of binary fractions: p (precision) is the total number of digits, and s (scale) is how many of those sit after the decimal point. DECIMAL(10, 2) stores up to 10 total digits, 2 after the point — exactly representing 12345678.90, with zero rounding error, forever. Rule of thumb used throughout the industry: if a number will ever be added, subtracted, or compared for exact equality — money, quantities, measurements that must reconcile — use DECIMAL/NUMERIC. Use FLOAT/DOUBLE only for values where tiny imprecision is acceptable, like a sensor reading or a machine-learning score.

NULL: the value that means "nothing here"

Every column type above describes what a known value looks like. But what if a value is simply unknown or doesn't apply? A student who hasn't taken an exam yet doesn't have a score of 0 — zero is a real, wrong answer. They have no score. SQL represents this with a special marker: NULL. NULL is not zero, not an empty string, and not false. It specifically means "unknown" or "not applicable." This sounds simple but has a genuinely tricky consequence: any comparison against NULL returns neither true nor false — it returns "unknown." marks = NULL never matches anything, even rows where marks genuinely is NULL — because "is this unknown value equal to this other unknown thing?" is itself unknown, and SQL only returns rows where a condition evaluates to true (not "unknown"). This is called three-valued logic: every condition in SQL evaluates to TRUE, FALSE, or UNKNOWN, and only TRUE rows survive a WHERE clause. $$\text{AND table} \quad \begin{array}{c|ccc} & T & F & U \\\hline T & T & F & U \\ F & F & F & F \\ U & U & F & U \\ \end{array} \qquad \text{OR table} \quad \begin{array}{c|ccc} & T & F & U \\\hline T & T & T & T \\ F & T & F & U \\ U & T & U & U \\ \end{array}$$ Notice F AND U = F (a false short-circuits, regardless of the unknown), but T AND U = U (unknown survives when nothing forces it either way). This is exactly how a real database engine evaluates every multi-condition WHERE clause — worth internalizing now, because forgetting it causes real, hard-to-spot bugs later (see below).

Common mistakes

1. Writing WHERE column = NULL. As shown above, this silently matches nothing, ever. Always use IS NULL / IS NOT NULL.
2. Using FLOAT for currency. Leads to values like 19.999999999998 appearing after enough arithmetic. Use DECIMAL.
3. Forgetting NOT NULL where it matters. By default, most columns allow NULL unless you say otherwise. A name column with no NOT NULL constraint will happily accept a row with no name at all — decide deliberately whether that's acceptable.
4. Assuming NOT IN works intuitively with NULL. WHERE id NOT IN (1, 2, NULL) returns zero rows, for every single row, because comparing against the NULL in that list makes the whole condition UNKNOWN. This trips up experienced engineers, not just beginners.

Summary

Data types are the database enforcing "this column can only ever hold this shape of value," catching bad data at the door rather than downstream. DECIMAL and FLOAT look similar but solve different problems — exactness versus compactness — and picking wrong for money is one of the most common real bugs in software. NULL means "unknown," not zero or empty, and SQL's three-valued logic (TRUE/FALSE/UNKNOWN) means comparisons against NULL never behave like ordinary comparisons — you must use IS NULL. With the shape of a single table now solid, the last lesson in this module gets your hands on a real, running database so the next module's SQL isn't just theory.