Views in MySQL

In many real-world applications, we do not always want users to access tables directly. Sometimes we want to:

  • Show only specific columns
  • Hide sensitive data
  • Simplify complex queries
  • Reuse common query logic

To solve this, MySQL provides Views.

1. What Is a View?

A view is a virtual table based on the result of a SQL query.

It does not store data itself.
Instead, it stores a query.

When you query a view, MySQL runs the underlying query and shows the result as if it were a table.

2. Why Use Views?

Views are useful for:

  • Simplifying complex joins
  • Restricting access to sensitive columns
  • Improving code readability
  • Providing abstraction from the actual table structure

For example, instead of writing a long join query every time, you can create a view and use it like a table.

3. Creating a View

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

Example:

CREATE VIEW active_customers AS

SELECT id, name, email

FROM customers

WHERE status = 'active';

Now you can use:

SELECT * FROM active_customers;

Just like a normal table.

4. Dropping a View

To delete a view:

DROP VIEW view_name;

Example:

DROP VIEW active_customers;

5. Updating Data Through a View

Some views are updatable, meaning you can use:

UPDATE view_name SET column = value;

However, not all views are updatable.

6. Updatable vs Non-Updatable Views

Updatable Views

A view is updatable if:

  • It is based on a single table
  • It does not use GROUP BY
  • It does not use DISTINCT
  • It does not use aggregate functions (SUM, COUNT, etc.)
  • It does not use joins.

Example of updatable view:

CREATE VIEW simple_view AS

SELECT id, name FROM students;

You can update it:

UPDATE simple_view SET name = 'Rahul' WHERE id = 1;

Non-Updatable Views

A view becomes non-updatable if it contains:

  • JOIN
  • GROUP BY
  • Aggregate functions
  • DISTINCT
  • Subqueries

Example:

CREATE VIEW sales_summary AS

SELECT customer_id, SUM(amount)

FROM orders

GROUP BY customer_id;

This view cannot be directly updated.

7. Views vs Tables

Feature

View

Table

Stores data

No

Yes

Based onthe  query

Yes

No

Physical storage

No

Yes

Can simplify queries

Yes

No

Performance

Depends on the query

Direct data access

Views do not store data separately. They show data stored in tables.

8. Advantages of Views

  1. Improved security
    • Hide sensitive columns like salary or passwords.
  2. Simplified queries
    • Avoid writing complex joins repeatedly.
  3. Logical data independence
    • Underlying table structure can change without affecting applications (if the view remains consistent).
  4. Better code readability

9. Limitations of Views

  1. Performance depends on the underlying query.
  2. Complex views may reduce performance.
  3. Some views cannot be updated.
  4. Too many nested views can make debugging difficult.

10. Real-World Example

In a company database:

  • HR department can see full employee data.
  • The sales team should see only the name and department.
  • The finance team should see the salary but not the personal details.

Instead of creating separate tables, you can create different views for each department.

Summary

A view is a virtual table based on a SQL query.

Key points:

  • Created using CREATE VIEW
  • Deleted using DROP VIEW
  • May be updatable or non-updatable
  • Used for security, simplicity, and abstraction
  • Does not store data physically

Views help organize and control how data is presented without changing the underlying tables.