Views

A view in MySQL is a virtual table that is based on the result of a SELECT query.

• It does not store data physically (unless you use WITH CHECK OPTION in certain cases).
• Acts like a shortcut to a query — you can treat it like a table.

Think of a view as a saved query that you can reuse.

Advantages of Views

• Simplicity: Complex queries can be saved as a view, so users can just query the view.
  • Instead of repeating a big join, you just SELECT * FROM employee_info.
• Security: You can restrict access to sensitive columns.
  • Example: Create a view that excludes the salary column and give users access only to that view.
• Reusability: One query definition can be reused many times.
• Data abstraction: Changes in table structure can be hidden behind a view.
  • Example: If table columns change, you can update the view definition, keeping applications working.
• Logical separation: Provides a layer between applications and base tables.

Limitations of Views

• Performance overhead: Views don’t store data; every time you query a view, MySQL executes the underlying query.

  • If the view has complex joins, performance may be slow.

• Dependency issues: If underlying tables change (dropped columns, renamed columns), views may break.

• No indexes directly on views: You cannot create an index on a view itself (only on underlying tables).

• Update restrictions: Not all views are updatable. You can update a view only if it’s based on a single table without: DISTINCT, GROUP BY, HAVING, UNION, Aggregate functions (SUM, AVG, etc.)

  CREATE VIEW dept_salary AS
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department;

  Cannot update this view, because it’s an aggregate.

Creating Views

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;

Example of Simple View: Create a view for all IT employees:

CREATE VIEW it_employees AS
SELECT emp_id, name, salary
FROM employees
WHERE department = 'IT';

Now you can query the view like a table:

SELECT * FROM it_employees;

View with Join

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(100)
);

CREATE VIEW employee_info AS
SELECT e.emp_id, e.name, d.dept_name, d.location
FROM employees e
JOIN departments d ON e.department = d.dept_name;

Now:

SELECT * FROM employee_info WHERE location = 'New York';

Using Views

Use View to update data:

UPDATE it_employees
SET salary = salary + 5000
WHERE emp_id = 101;

Modifying and Dropping Views

Modify:

CREATE OR REPLACE VIEW it_employees AS
SELECT emp_id, name, salary
FROM employees
WHERE department = 'IT' AND salary > 60000;

Drop:

DROP VIEW it_employees;