CASE and Views

Use CASE for conditional values

A searched CASE checks conditions in order and returns the result for the first true condition. If none match, ELSE supplies the fallback; without it, the result is NULL.

SELECT order_id,
       CASE
         WHEN total >= 100 THEN 'large'
         WHEN total >= 40 THEN 'medium'
         ELSE 'small'
       END AS size_band
FROM orders;

The order matters: a condition that catches too much can make later branches unreachable. Comparisons with NULL are not true, so add an explicit WHEN total IS NULL branch if missing totals need their own label. A simple CASE expression WHEN value form is useful for equality checks, but cannot express ranges.

Conditional aggregation combines CASE with aggregates, for example SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END). For a portable alternative, some engines also support FILTER; check the target dialect.

A view is a saved query

Create a view when a query expresses a stable, reusable concept such as active customers:

CREATE VIEW active_customers AS
SELECT customer_id, name
FROM customers
WHERE status = 'active';

Selecting from the view runs its underlying query; a regular view is not automatically a stored copy of the results. Updates through views have engine-specific restrictions. A materialized view stores results and needs a refresh strategy; SQLite does not provide native materialized views.

Views can reduce repetition and restrict which columns users see, but they are not a substitute for permissions on the underlying data. Keep the view definition clear, and inspect its query plan when it becomes slow. See query plans.

Keep branch results compatible

The result branches of a CASE expression should have compatible types. If one branch returns a number and another returns text, the database may coerce values or reject the expression depending on its type rules. Give the expression a meaningful alias so callers can understand the derived field. A view can also expose a stable set of columns while hiding joins from consumers, but changing its output columns can break dependent queries. Treat view definitions as part of the interface and migrate them deliberately.

Further reading

• SQLite expressions documents CASE evaluation.
• SQLite CREATE VIEW describes view behavior.