Going Deeper: Professional SQL›Extra· 35 min read

Views & CASE Expressions

Save a complex query as a reusable virtual table, and add computed, categorised columns with CASE.

What you will learn

Create and use a VIEW to reuse a query
Understand that a view stores the query, not the data
Build conditional columns with CASE WHEN

A view is a saved query

When you find yourself writing the same long query again and again — a JOIN with filters and calculations — you can save it under a name as a view. A view behaves like a table you can SELECT from, but it stores the query, not the data. Every time you read the view, MySQL re-runs the underlying query against the current rows, so a view is always up to date.

Suppose this report — “each order with its customer’s name and city” — is one you run constantly:

Save a JOIN query as a reusable view

CREATE VIEW order_summary AS
SELECT orders.id, users.name, users.city, orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id;

Line by line: CREATE VIEW order_summary AS gives the query a name. Everything after AS is an ordinary SELECT — the exact JOIN you would otherwise retype. Nothing is copied or duplicated; MySQL just remembers the query under the name order_summary.

Note: Output: Query OK, 0 rows affected. The view is created. No data was stored — only the query definition. If a new order is added tomorrow, the view will include it automatically, because it re-runs the SELECT each time you use it.

Now you query the view as if it were a table — short and readable:

Read from the view like any table

SELECT * FROM order_summary WHERE city = 'Mumbai';

MySQL runs the saved JOIN behind the scenes, then applies your WHERE city = 'Mumbai' filter on top. You got the full report logic without retyping the JOIN — and you can filter, sort and group a view exactly like a real table.

Note: Output: id name city total 101 Asha Mumbai 500 103 Asha Mumbai 900 The view supplied the joined columns; your filter kept only Mumbai rows. To remove a view later you use DROP VIEW order_summary; — it deletes only the saved query, never the underlying tables.

Tip: Views are great for three things: reuse (write the hard query once), simplicity (give teammates a clean named report instead of a scary JOIN), and security (expose a view with only the safe columns and hide the rest of the table).

CASE — a computed, categorised column

Often a report needs a column that does not exist in the table — a label that depends on the data, like tagging each order “Big”, “Medium” or “Small” by its total. CASE is SQL’s if/else: it checks conditions in order and returns the first matching result.

Bucket each order into a size category with CASE

SELECT
  id,
  total,
  CASE
    WHEN total >= 800 THEN 'Big'
    WHEN total >= 300 THEN 'Medium'
    ELSE 'Small'
  END AS size_label
FROM orders;

Reading the CASE: for each row MySQL checks the WHEN conditions top to bottom and returns the result of the first one that is true. A total of 900 hits the first WHEN (≥ 800) → “Big”. A total of 500 skips the first, matches the second (≥ 300) → “Medium”. A total of 150 matches neither, so the ELSE gives “Small”. END AS size_label closes the CASE and names the new column.

Note: Output (against totals 500, 250, 900, 300, 150): id total size_label 101 500 Medium 102 250 Small 103 900 Big 104 300 Medium 105 150 Small A brand-new size_label column appeared, computed from each total. Order matters in CASE: the first matching WHEN wins, so always list the most specific or highest thresholds first.

Tip: Two handy shortcuts related to CASE: IFNULL(value, fallback) returns fallback when value is NULL, and COALESCE(a, b, c) returns the first non-NULL of several values — both are perfect for replacing blank data with a sensible default in reports.

Q. What does a VIEW actually store?

Answer: A view stores the SELECT query, not the rows. Each time you query the view, MySQL runs the saved query against the current data, so a view is always up to date.

✍️ Practice

Create a view that joins two of your tables and select from it with a WHERE filter.
Add a CASE column that labels rows into categories (e.g. price bands).

🏠 Homework

Build a view for a report you would run often, then write a CASE expression inside it to add a categorised column.