Views

By now you've written some genuinely useful queries against StreamHub that join several tables and aggregate the results - like calculating each movie's average rating. Retyping a query like that every time you need it is tedious, and error-prone if two developers write slightly different versions of "the same" query. A MySQL view solves this by letting you save a query under a name and reuse it as if it were a table.

What a View Actually Is

A view is a saved, reusable SELECT query that behaves like a virtual table. The important part to understand clearly: a view doesn't store its own copy of the data. It just remembers the query, and every time you select from the view, MySQL re-runs that underlying query against the real tables and returns fresh results. This means a view is always up to date with the current data - there's no separate copy that could go stale.

CREATE VIEW - Saving a Query as a View

Here we take the join-and-aggregate query for a movie's average rating - the exact kind of query built up across earlier lessons - and save it permanently as a view called movie_ratings:

CREATE VIEW movie_ratings AS
SELECT m.id, m.title, AVG(r.rating) AS avg_rating, COUNT(r.id) AS review_count
FROM movies m
LEFT JOIN reviews r ON r.movie_id = m.id
GROUP BY m.id;

Notice the LEFT JOIN - it keeps every movie in the result even if it has zero reviews yet, in which case avg_rating comes back NULL and review_count comes back 0.

Querying a View Like a Normal Table

Once created, movie_ratings can be queried exactly like any other table - filtered, sorted, whatever you need:

SELECT * FROM movie_ratings WHERE avg_rating > 4;

Compare how much simpler this reads next to writing out the full join and GROUP BY every single time you needed "movies rated above 4" somewhere in your application - the view hides all of that behind one plain WHERE clause.

Why Views Are Genuinely Useful

Two real benefits stand out. First, a view hides query complexity behind a simple name - anyone writing application code against movie_ratings doesn't need to know or care that it's secretly a join plus an aggregate. Second, and just as important, a view gives everyone on a team a single, consistent definition of "what counts as a movie's rating." Without it, one developer might average only ratings from the last year, another might include every rating ever left, and the two parts of the application would quietly disagree with each other. Defining it once as a view removes that risk entirely.

CREATE OR REPLACE VIEW - Updating a View's Definition

Requirements change - maybe StreamHub decides a movie needs at least 5 reviews before its average rating counts as meaningful. CREATE OR REPLACE VIEW redefines an existing view in place without needing to drop it first:

CREATE OR REPLACE VIEW movie_ratings AS
SELECT m.id, m.title, AVG(r.rating) AS avg_rating, COUNT(r.id) AS review_count
FROM movies m
LEFT JOIN reviews r ON r.movie_id = m.id
GROUP BY m.id
HAVING COUNT(r.id) >= 5;

Every place in the application that already selects from movie_ratings automatically picks up the new definition - nothing else needs to change.

DROP VIEW - Removing a View

When a view is no longer needed, DROP VIEW removes its saved definition. Since a view never held its own copy of data, dropping it is safe in the sense that it can't delete anything from the underlying movies or reviews tables:

DROP VIEW movie_ratings;

An Honest Limitation - Views Aren't Always Updatable

It's tempting to assume you can INSERT or UPDATE through a view just like a table, but that's only true for simple, single-table views with no joins or aggregation. The moment a view involves a JOIN (like movie_ratings does) or an aggregate function like AVG() or COUNT(), MySQL generally can't figure out how to translate a write back into a change on the underlying tables - there's no single row in movies or reviews that an averaged, grouped value like avg_rating corresponds to. In practice, movie_ratings is read-only.

When Not to Reach for a View

Views earn their keep when a query is reused in multiple places or represents an important, shared concept worth naming consistently. If a query is genuinely a one-off - something you'll run once to answer a single question and never again - a plain query is simpler than creating and later remembering to maintain a view for it.