What determines the data type of a CASE expression whose branches return different types?

## One expression, one type SQL is a typed language at the column level: every column of a result set has one declared data type, for every row. A `CASE` is an expression that can produce a column, so it must resolve to a single type too. The engine derives that type from the **set of result expressions** — all the `THEN` results plus the `ELSE` result — and applies it to the whole CASE. It does not vary per row, and it is not simply the type of whichever branch happened to fire. ## Compatible branches: widening When the branch types belong to the same family, the engine picks a type that can hold all of them: ```sql CASE WHEN discounted THEN 0.5 ELSE 1 END ``` One branch is exact numeric with a fractional part, the other an integer; the result is numeric with a scale, not an integer. This matters because the *outcome* changes: if you expected an integer result you may be surprised by `1.0` in the output, and if you expected a decimal you must ensure at least one branch supplies the scale. The same widening logic applies among character types, where the result is a character type long enough for the branches. A related subtlety: the declared length or precision of the result generally follows the **branch types**, not the values actually returned on a given row. A branch returning a literal declared as a short character type can therefore constrain a result that another branch would have filled with something longer. Engines differ in how aggressively they widen, so if the exact declared type matters — a view definition, a `CREATE TABLE AS SELECT`, a client that reads metadata — pin it with an explicit `CAST` rather than trusting the derivation. ## Incompatible branches: a type error ```sql SELECT order_id, CASE WHEN shipped_at IS NULL THEN 'pending' ELSE shipped_at END FROM orders; ``` Here one branch is character data and the other a timestamp. There is no reasonable common type, so a strictly typed engine rejects the statement — at parse or plan time, before any row is examined, which is a mercy: the failure is deterministic rather than data-dependent. Fix it by deciding what the column means and casting to that: ```sql SELECT order_id, CASE WHEN shipped_at IS NULL THEN 'pending' ELSE CAST(shipped_at AS VARCHAR(30)) END AS shipped_label FROM orders; ``` If you instead want a real timestamp column with a missing marker, the honest answer is that the marker must be NULL — a timestamp column cannot hold the word `'pending'`. That is often the better design: keep the typed column typed, and derive the label separately for display. SQLite is the notable exception to the strictness: its type system attaches affinities to columns rather than enforcing expression types, so the mixed-branch CASE runs and yields values of different storage classes per row. Portable code should not rely on that. ## The NULL branch A bare `NULL` literal in a branch — or the implicit `ELSE NULL` — carries no type of its own, so it takes the type derived from the other branches. That is usually what you want. Where every result is NULL, or where a NULL branch meets an untyped parameter marker, engines may be unable to infer anything and will ask you to be explicit; `CAST(NULL AS DATE)` resolves it. ## Where the derived type escapes the query The derived type is not a local detail. It becomes: - the column type of a view built over the query; - the column type created by `CREATE TABLE AS SELECT`; - the type reported through result-set metadata, which client drivers use to choose how to materialise the value; - the type used when the CASE result is compared or joined against something else, which can introduce an implicit conversion. So an unpinned CASE type propagates: a query that today produces a wide-enough character column can start truncating or start returning a different numeric scale after someone edits an unrelated branch. Explicit casts in the branches make the contract visible where it is written. ## Practical rules Write branches that already share a type wherever you can — mapping codes to labels, or values to ranks, keeps the whole CASE in one family. When branches genuinely differ, cast each of them, not just the odd one out, so a reader can see the intended result type without knowing the engine's precedence table. Prefer keeping typed data typed and moving presentation strings to the display layer; a column that is sometimes a date and sometimes the word `'pending'` is a formatting decision that has leaked into the data. And when a CASE feeds a table or a view definition, treat the cast as mandatory: that is the point where the derived type stops being a query detail and becomes schema.