CASE and Views

Start here

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.

CASE: if/else inside a query

CASE is SQL's conditional expression. It returns a value, so it can appear anywhere a value can — in SELECT, in ORDER BY, inside an aggregate, even in WHERE.

SELECT name,
       salary,
       CASE
         WHEN salary >= 80000 THEN 'senior'
         WHEN salary >= 50000 THEN 'mid'
         ELSE 'junior'
       END AS band
FROM   employees;

Two rules that explain most CASE behaviour. Conditions are evaluated in order and the first match wins — so overlapping conditions are fine as long as the more specific one comes first. And with no ELSE, unmatched rows get NULL, which is a common source of unexpected blanks.

There is also a shorter form for equality against one expression:

CASE status WHEN 'A' THEN 'Active' WHEN 'C' THEN 'Closed' ELSE 'Unknown' END

Conditional aggregation: the pattern worth learning

Putting CASE inside an aggregate turns rows into columns, which is how summary reports are built without a pivot feature.

SELECT region,
       COUNT(*)                                                  AS total,
       SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END)      AS completed,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)     AS cancelled,
       AVG(CASE WHEN status = 'complete' THEN total_amount END)  AS avg_completed
FROM   orders
GROUP  BY region;

Note the last line: with no ELSE, non-complete orders become NULL, and AVG skips NULLs — so the average is computed over completed orders only. That interaction between CASE and aggregate NULL-skipping is the trick that makes this pattern so compact.

PostgreSQL offers a cleaner spelling with FILTER:

COUNT(*) FILTER (WHERE status = 'complete') AS completed

CASE is also how you sort in a business order rather than alphabetically, and how you avoid division by zero (CASE WHEN n = 0 THEN NULL ELSE x / n END, though NULLIF is neater).

Views: a name for a query

A view is a stored SELECT that behaves like a table:

CREATE VIEW active_customers AS
SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count
FROM   customers c
LEFT   JOIN orders o ON o.customer_id = c.id AND o.order_date > CURRENT_DATE - 365
WHERE  c.status = 'active'
GROUP  BY c.id, c.name, c.email;

SELECT * FROM active_customers WHERE order_count > 5;

It stores no data. Every query against it re-runs the underlying SELECT, with the outer conditions merged in by the planner — so the filter above is applied efficiently rather than after materialising every active customer.

Views earn their keep in three ways: they hide complexity behind a name, they present a stable interface while the tables underneath change, and they restrict access — grant a user the view instead of the table and they see only the rows and columns it exposes.

Their limits are worth knowing too. A simple view can be updatable; anything with joins, aggregates or DISTINCT generally is not, and needs an INSTEAD OF trigger to accept writes. And views stack: a view built on three views built on four views is a maintenance problem and often an unpredictable query plan.

Branching inside a query, and naming one for reuse

CASE is an expression, not a statement, which is why it can appear anywhere a value can -- including inside an aggregate, where it becomes a conditional count. A view then gives a query a name, and the important thing about it is what it does not do.

-- CASE as a value in the SELECT list.

SELECT name,
       price,
       CASE WHEN price >= 100 THEN 'premium'
            WHEN price >=  40 THEN 'standard'
            ELSE                   'budget'
       END AS tier
FROM products
ORDER BY price DESC;

-- CASE inside an aggregate is the useful trick: it turns one pass over
-- the table into several conditional counts, which is how you pivot.

SELECT count(*)                                             AS all_orders,
       sum(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
       sum(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       count(discount)                                      AS with_discount
FROM orders;

-- Note the last column. count(discount) counts non-NULL values, while
-- count(*) counts rows -- which is the shortest way to count how many
-- rows have a value in a nullable column.
--
-- A VIEW names a query. It stores no data.

CREATE VIEW order_totals AS
SELECT o.id           AS order_id,
       c.name         AS customer,
       o.status,
       sum(i.qty * p.price) AS gross
FROM orders o
JOIN customers   c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN products    p ON p.id = i.product_id
GROUP BY o.id, c.name, o.status;

SELECT * FROM order_totals ORDER BY gross DESC;

-- The view is re-run every time you select from it. It costs nothing to
-- create and saves nothing at query time -- it is a name for a query,
-- not a cache of its answer. The thing that caches is a MATERIALIZED
-- view, which SQLite does not have and which every engine that does have
-- one makes you decide when to refresh.

SELECT customer, count(*) AS orders, round(sum(gross), 2) AS lifetime
FROM order_totals
GROUP BY customer
ORDER BY lifetime DESC;

Guided experiments

1. Read the bands. Each row gets a label from CASE based purely on its salary and the two thresholds.
2. 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.
3. 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.
4. 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.

Where that leaves you

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.

Materialised views

A materialised view stores the result rather than re-running it:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
FROM   orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Queries against it are as fast as querying a table, because it is one. The trade is staleness: the data is only as current as the last refresh, and the refresh itself costs whatever the underlying query costs.

Use one when an expensive aggregate is read far more often than the data changes — dashboards, reports, leaderboards. CONCURRENTLY lets readers keep using the old copy while the new one builds, at the cost of requiring a unique index on the view.

MySQL has no materialised views; the usual substitute is a summary table maintained by a scheduled job or triggers.

Common mistakes

• Forgetting ELSE and getting NULLs where a default was intended.
• Ordering conditions wrongly. WHEN salary > 50000 THEN 'mid' WHEN salary > 80000 THEN 'senior' never returns 'senior', because the first condition already caught those rows.
• Mixing return types across branches. Some engines coerce, some raise an error, and the coercion is rarely what you want.
• Views over views over views. Convenient to write, painful to debug, and easy for the planner to handle badly.
• Treating a view as a cache. It is not one — that is what a materialised view or a summary table is for.
• Putting a CASE on an indexed column in WHERE, which prevents the index being used.

Questions people ask

Can CASE be used in ORDER BY? Yes, and it is the standard way to sort by a custom business order.

Are views slower than the equivalent query? No — the planner inlines the definition and optimises the whole thing together.

Can I update through a view? Simple single-table views, usually yes. Anything with a join or aggregate needs an INSTEAD OF trigger.

Do views improve security? Yes, genuinely — granting access to a view instead of a table is a standard way to expose a subset of columns or rows.

How often should a materialised view refresh? As rarely as the freshness requirement allows. Hourly for most dashboards; nightly for reporting. Refresh cost and staleness tolerance are the only two inputs.

Is COALESCE just a shorter CASE? Effectively yes: COALESCE(a, b) is CASE WHEN a IS NOT NULL THEN a ELSE b END. Use COALESCE when that is all you need.

Recap in one screen

• CASE returns a value and can appear anywhere a value can; the first matching condition wins.
• With no ELSE, unmatched rows return NULL — often useful inside aggregates, often a bug elsewhere.
• Conditional aggregation (SUM(CASE WHEN ...)) turns rows into report columns.
• A view names a query and stores nothing; it hides complexity and can restrict access.
• A materialised view stores the result and needs refreshing — use it when reads far outnumber changes.