14. MySQL VIEW: Complete Beginner-to-Advanced Guide with Examples

In MySQL, a VIEW is a virtual table based on the result of a SELECT query. It acts like a stored query that you can reference just like a regular table, simplifying complex queries and enhancing security and modularity.

✅ What is a VIEW?

A view is a named query stored in the database. It does not store data physically but pulls from underlying tables each time it is queried.

📌 Syntax:

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;

📌 Example Table – employees

🔹 Creating a Basic VIEW

Example 1: Create a View of Active Employees with Department ID

CREATE VIEW active_employees AS
SELECT employee_id, name, department_id
FROM employees
WHERE department_id IS NOT NULL;

Use the View:

SELECT * FROM active_employees;

Result: Returns only employees with assigned departments.

🔹 Creating JOIN-based VIEW

Example 2: Employee Name and Department Name

Assume departments table:

CREATE VIEW employee_department AS
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Query:

SELECT * FROM employee_department;

🔹 Updating Data Through a View

Some views can be updatable, but with conditions:

• Only one base table
• No aggregates, DISTINCT, GROUP BY, or joins

Example 3: Update Through View

CREATE VIEW basic_view AS
SELECT employee_id, name FROM employees;

UPDATE basic_view
SET name = 'Robert'
WHERE employee_id = 2;

✅ This works because the view is based on a single table with direct columns.

🔹 Read-Only Views

Views with JOINs, aggregates, or GROUP BY cannot be updated.

Example 4: Read-only View with Aggregate

CREATE VIEW department_summary AS
SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

This view cannot be updated directly.

🔹 Dropping a View

DROP VIEW view_name;

Example:

DROP VIEW active_employees;

🔹 Modifying a View

CREATE OR REPLACE VIEW view_name AS
SELECT ...

Example:

CREATE OR REPLACE VIEW basic_view AS
SELECT employee_id, name, manager_id FROM employees;

🔹 Benefits of Using Views

• Simplify complex SQL queries.
• Improve security by hiding sensitive columns.
• Encapsulate logic: Useful for business rules and reusability.
• Enable modular design: Acts like an abstraction layer.

✅ Summary Table

🧪 Practice Tasks

1. Create a view that lists employees without a manager.
2. Create a view to count employees in each department.
3. Try updating a basic view and see the effect.
4. Replace a view with new logic using CREATE OR REPLACE.