MySQL Views

A view in MySQL is a saved SQL query stored with a name in the database. It behaves like a virtual table — data is not physically stored in the view. Instead, every time the view is queried, MySQL runs the underlying SELECT statement and returns the result. Views simplify complex queries, improve security, and provide a consistent interface for accessing data.

Why Use Views?

• Simplify complex queries — A complicated JOIN or aggregation can be stored as a view and queried simply.
• Restrict data access — Users can be given access to a view that shows only certain columns, hiding sensitive data.
• Reusability — A view defined once can be used in multiple queries.
• Consistency — Business logic is defined once in the view rather than repeated in every query.

Creating a View

Syntax

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Sample Tables

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 'IT', 72000, 'Delhi');
INSERT INTO employees VALUES (2, 'Sneha Joshi', 'HR', 45000, 'Mumbai');
INSERT INTO employees VALUES (3, 'Karan Das', 'IT', 88000, 'Delhi');
INSERT INTO employees VALUES (4, 'Pooja Nair', 'Finance', 61000, 'Chennai');

Example: Create a View for IT Employees

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

Query the View

SELECT * FROM it_employees;

Result:

emp_id | emp_name   | salary
-------+------------+--------
1      | Ravi Kumar | 72000.00
3      | Karan Das  | 88000.00

The view returns only IT employees. The HR and Finance rows are never visible through this view.

View with JOIN

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(50)
);
INSERT INTO departments VALUES (1, 'IT', 'Delhi');
INSERT INTO departments VALUES (2, 'HR', 'Mumbai');

CREATE VIEW emp_dept_view AS
SELECT e.emp_name, e.salary, d.location
FROM employees AS e
JOIN departments AS d ON e.department = d.dept_name;

SELECT * FROM emp_dept_view;

Updating a View

CREATE OR REPLACE VIEW it_employees AS
SELECT emp_id, emp_name, salary, city
FROM employees
WHERE department = 'IT';

CREATE OR REPLACE VIEW replaces the existing view definition without dropping it first.

Dropping a View

DROP VIEW it_employees;

Showing All Views

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Updatable Views

Simple views (based on one table, no aggregation, no DISTINCT) can allow INSERT, UPDATE, and DELETE operations. Complex views (with JOINs, GROUP BY, or aggregate functions) are read-only.

-- Updating data through a simple view
UPDATE it_employees SET salary = 75000 WHERE emp_id = 1;

Key Points

• A view is a saved SELECT query that acts as a virtual table.
• Views do not store data — they retrieve it from the base tables every time.
• Use CREATE OR REPLACE VIEW to update an existing view.
• Views can restrict data access by exposing only specific columns or rows.
• Simple views support DML operations; complex views are read-only.