LearnContact
Lesson 5318 min read

Views

Learn how to create and use views — saved, named queries that behave like virtual tables — to simplify complex queries and control visible data.

Introduction

As queries grow more complex — joining several tables, filtering, grouping — retyping the same long query every time becomes tedious and error-prone. MySQL lets you save a query under a name and reuse it as if it were a table. This saved query is called a view.

This lesson covers what a view is, how to create and query one, and the practical reasons developers rely on them daily.

What You Will Learn
  • What a view is and how it differs from a real table.
  • How to create a view with CREATE VIEW.
  • How to query a view exactly like a table.
  • Why views are useful for simplifying queries and restricting visible data.
  • How to remove a view with DROP VIEW.

What is a View?

A view is a saved, named SELECT query stored inside the database. It behaves like a virtual table: you can SELECT from it, filter it, and join it to other tables — but it does not store any data of its own. Every time you query a view, MySQL runs its underlying SELECT statement behind the scenes and returns fresh results.

Simple Definition
  • A view looks and behaves like a table when you query it.
  • A view stores no data itself — it is just a saved query.
  • Every time you query a view, its underlying query runs again, so results are always current.

Creating a View

A view is created with CREATE VIEW, followed by a name and an AS clause containing the SELECT statement to save.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    marks INT,
    email VARCHAR(100)
);

INSERT INTO students VALUES
    (1, 'Alex', 78, 'alex@school.com'),
    (2, 'Sam', 45, 'sam@school.com'),
    (3, 'Priya', 92, 'priya@school.com');

CREATE VIEW top_students AS
SELECT name, marks
FROM students
WHERE marks >= 60;

This creates a virtual table named "top_students" that always reflects students currently scoring 60 or above — without duplicating any data.

Querying a View

Once created, a view is queried exactly like a regular table, using a normal SELECT statement.

SELECT * FROM top_students;
Result
+-------+-------+
| name  | marks |
+-------+-------+
| Alex  | 78    |
| Priya | 92    |
+-------+-------+

You can also filter or sort a view further, just like any table.

SELECT * FROM top_students
ORDER BY marks DESC;
Result
+-------+-------+
| name  | marks |
+-------+-------+
| Priya | 92    |
| Alex  | 78    |
+-------+-------+

Why Use Views?

Views solve two very common practical problems.

Simplify Complex Queries

Save a long query involving multiple JOINs and WHERE clauses once, then reuse it with a simple SELECT * FROM view_name.

Restrict Visible Columns

Expose only certain columns through a view (e.g. hide a "salary" or "password" column) so users querying the view never see sensitive data.

Reduce Repetition

Avoid copy-pasting the same complex query across many reports and applications.

Improve Readability

A well-named view like monthly_sales_summary is far easier to read than the raw query behind it.

For example, instead of every application repeating a three-table JOIN to find each student's latest grade, you save that JOIN once as a view and everyone queries the simple view instead.

CREATE VIEW student_contacts AS
SELECT name, email
FROM students;

Here, "student_contacts" exposes only name and email — hiding the marks column entirely from anyone who only has access to the view.

Updating and Dropping Views

To change what a view does, use CREATE OR REPLACE VIEW with the same name and a new SELECT statement. To remove a view entirely, use DROP VIEW.

CREATE OR REPLACE VIEW top_students AS
SELECT name, marks
FROM students
WHERE marks >= 70;

DROP VIEW top_students;
Dropping a View

DROP VIEW removes only the saved query definition — it never deletes any actual data, since a view never stored data to begin with.

Common Mistakes

Avoid These Mistakes
  • Thinking a view stores a physical copy of data — it re-runs its query every time instead.
  • Forgetting that a view will break if a table or column it depends on is renamed or dropped.
  • Assuming every view is automatically updatable with INSERT/UPDATE — only simple, single-table views generally are.
  • Overusing views for one-off queries that will never be reused.

Best Practices

  • Create a view for any query that gets reused across multiple reports or applications.
  • Use views to restrict which columns different users or applications can see.
  • Give views clear, descriptive names, like active_customers or monthly_revenue.
  • Keep in mind a view is only as fast as the query behind it — a slow underlying query means a slow view.

Frequently Asked Questions

Does a view store its own data?

No. A view stores only the SELECT statement definition. Every query against the view re-runs that SELECT statement against the real underlying tables.

Can I INSERT or UPDATE through a view?

Sometimes. Simple views based on a single table without GROUP BY, DISTINCT, or joins are often updatable, but views built from complex queries typically are not.

Are views slower than querying tables directly?

A view itself adds negligible overhead — its speed is really just the speed of the query it wraps, since that query runs every time the view is used.

Can a view be built from other views?

Yes, though it is best kept simple — deeply nested views (views built on views on views) can become hard to reason about and debug.

Key Takeaways

  • A view is a saved, named query that behaves like a virtual table.
  • CREATE VIEW view_name AS SELECT ... defines a view.
  • A view is queried with a normal SELECT * FROM view_name.
  • Views simplify complex, frequently-used queries and can restrict which columns are visible.
  • DROP VIEW removes a view definition without touching any real data.

Summary

Views let you save a query once and reuse it as if it were a table, without duplicating any data. They are one of the simplest ways to simplify complex SQL and control what data different users can see.

In this lesson, you learned what a view is, how to create, query, replace, and drop one, and why views are so useful in real applications. Next, you will learn about indexes — a tool for making lookups on large tables much faster.

Lesson 53 Completed
  • You understand what a view is and how it works.
  • You can create a view with CREATE VIEW.
  • You can query a view like any regular table.
  • You are ready to learn about indexes.
Next Lesson →

Indexes