Views

Introduction

A view is a saved SELECT query that looks like a table—useful for simplifying complex joins, hiding columns, or granting read-only access without exposing base tables. This chapter creates, queries, updates, and drops views on the blog_dev schema.

Prerequisites

Create a View

Published posts with author email:

sql

CREATE VIEW v_published_posts AS
SELECT
  p.id AS post_id,
  p.title,
  p.published_at,
  u.display_name AS author,
  u.email AS author_email
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL;

Query like a table:

sql

SELECT post_id, title, author FROM v_published_posts
ORDER BY published_at DESC
LIMIT 5;

Code explanation:

View stores definition, not materialized data—runs underlying query each time
Upstream data changes reflect immediately in view results

Show and Inspect Views

sql

SHOW FULL TABLES IN blog_dev WHERE TABLE_TYPE = 'VIEW';
 
SHOW CREATE VIEW v_published_posts\G

Replace or Alter View

Replace entire definition:

sql

CREATE OR REPLACE VIEW v_published_posts AS
SELECT
  p.id AS post_id,
  p.title,
  p.published_at,
  u.display_name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL
  AND u.is_active = 1;

MySQL has no ALTER VIEW—use CREATE OR REPLACE VIEW.

Drop View

sql

DROP VIEW IF EXISTS v_published_posts;

Does not delete base table data.

Updatable Views (Limited)

Simple views on one table may allow insert/update/delete if MySQL can map row to base table:

sql

CREATE VIEW v_active_users AS
SELECT id, email, display_name
FROM users
WHERE is_active = 1;

sql

UPDATE v_active_users SET display_name = 'New Name' WHERE id = 1;

Complex joins, aggregates, DISTINCT, or expressions in select list make view read-only.

Check:

sql

-- Information schema flags updatable views
SELECT TABLE_NAME, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'blog_dev';

Warning

Do Not Rely on Views for Write Logic

Apps usually write to base tables; views for read simplification and security.

Security Use Case

Grant select on view only—hide email from analysts:

sql

CREATE VIEW v_posts_public AS
SELECT p.id, p.title, p.published_at, u.display_name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL;
 
-- GRANT SELECT ON blog_dev.v_posts_public TO 'analyst'@'localhost';

Permissions chapter expands GRANT.

Views vs Materialized Views

MySQL has no native materialized view—results computed on each query. For heavy aggregates, use summary tables updated by jobs or consider other databases' features.

Pros and Cons

Pros	Cons
Simplify repeated SQL	No stored performance magic
Encapsulate business rules	Can hide complexity from optimizer hints
Column-level access control	Updatable rules confusing

FAQ

View slow?

Underlying query cost remains—index base tables.

Order BY in view?

Allowed; outer query may reorder anyway.

View on view?

Yes—stack definitions; debug gets harder.

CASCADE drop?

Dropping base table may fail if views depend—drop views first.

WITH CHECK OPTION?

sql

CREATE VIEW v_drafts AS
SELECT * FROM posts WHERE published_at IS NULL
WITH CHECK OPTION;

Inserts/updates through view must satisfy WHERE—prevents publishing via view accidentally.

ORM and views?

Many ORMs map read-only entities to views.