CASE and Views
CASE turns a raw value into a label, row by row. A view saves that whole query under a name — and stays a query, not a copy of the answer.
Salary Bands
The View
Create the view first, then try the raise.
employees, with a CASE column
—Query
Counts
CASE and Views: A Practical Guide
A branch inside SELECT, and a name for a query.
Quick Context
CASE is an if/else that lives inside a SELECT list, evaluated once per row. A VIEW is a query with a name, so you can SELECT from it like a table without repeating the logic every time.
CASE, shape and rules
CASE WHEN cond1 THEN v1 WHEN cond2 THEN v2 ELSE v3 END
Conditions are checked top to bottom and the first match wins — order matters when ranges overlap. Without an ELSE, a row matching nothing gets NULL, silently, which is a common source of "why is this column empty" bugs.
Views: a name for a query, not a copy of it
A plain view stores no data. Every time you query it, the underlying SELECT runs again against the current table — which is the whole point of the raise experiment below. This is different from a materialized view, which does store a snapshot and has to be refreshed explicitly to catch up with changes underneath it.
Interactive Exploration Guide
- Read the bands. Each row gets a label from CASE based purely on its salary and the two thresholds.
- Move a threshold. Drag Senior down and someone's label flips from mid to senior immediately — the CASE logic is re-evaluated on every row, every time.
- Create the view. Press the button. A second panel appears, querying pay_bands — identical to the first table, because it is the same logic under a name.
- Give Grace a raise. Her underlying salary changes, and pay_bands updates on the very next read — no refresh, no re-creation. The view is not a stored answer, it is the query, run again.
Key Takeaway
CASE is a per-row branch inside SELECT that returns the first matching value or NULL if nothing matches and there is no ELSE. A view is a saved query, not saved data — it re-runs against live tables on every read, which is what makes it stay correct as the underlying data changes and is exactly the property a materialized view trades away for speed.