Views for clarity

Views for clarity: saved SELECT statements you query like tables. They tidy up complex joins, give stable names to reporting logic, and can restrict what less-privileged users see.

What a view is

A view has no stored data of its own (unless materialised – uncommon in MySQL). Each query against a view runs the underlying SELECT. Think of it as a named, reusable query.

CREATE VIEW v_order_summary AS
SELECT
  o.id AS order_id,
  o.placed_at,
  o.status,
  c.name AS customer_name,
  c.email AS customer_email,
  (
    SELECT SUM(oi.quantity * oi.unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.id
  ) AS order_total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

SELECT * FROM v_order_summary WHERE status = 'paid';

Why use views

• Readability – report writers query v_order_summary instead of a ten-line JOIN
• Consistency – order total calculated one way everywhere
• Security – expose a view without columns containing salary or internal notes
• Abstraction – rename or split underlying tables; update the view, keep app queries stable (within reason)

Simple reporting view

CREATE VIEW v_product_sales AS
SELECT
  p.id AS product_id,
  p.name AS product_name,
  SUM(oi.quantity) AS units_sold,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
INNER JOIN order_items oi ON oi.product_id = p.id
INNER JOIN orders o ON o.id = oi.order_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY p.id, p.name;

Dashboards and ad-hoc reports hit the view. Change the status filter once in the view definition, not in fifteen copied queries.

Updatable views (careful)

Simple views on one table can sometimes accept INSERT/UPDATE/DELETE. Complex views with JOINs, GROUP BY, or subqueries are read-only. Do not rely on updatable views for app logic unless you understand MySQL’s rules – application code against base tables is usually clearer.

Managing views

SHOW FULL TABLES WHERE table_type = 'VIEW';

CREATE OR REPLACE VIEW v_order_summary AS
SELECT ... ;

DROP VIEW IF EXISTS v_order_summary;

Views vs CTEs

CTEs live inside one query; views persist in the database for anyone to use. Use a CTE for a one-off complex report in a script. Use a view when the same shape is queried from apps, BI tools, or multiple developers.

Performance note

Views are not magic caching – heavy views on large tables still run heavy queries. EXPLAIN the view query if it is slow. Materialised views (precomputed tables refreshed on a schedule) are a pattern you build yourself in MySQL, not a built-in feature like some other databases.

The next tutorial covers stored procedures and functions – when moving logic into the database is worth it, and when it is not.