What is a View?
In MySQL, a View is like a virtual table that represents the result of a SQL query. It doesn’t store any data physically — it just displays data fetched from one or more tables, based on the query logic used to create the view.
Views are useful when you want to hide complexity, restrict access to sensitive columns, or reuse a common query. For example, if you often query active users from a large table with conditions, you can create a view and use it like a regular table without rewriting the logic.
General Syntax:-
CREATE [OR REPLACE] 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, querying the view:
SELECT * FROM active_customers;
( This returns all customers with an ‘active’ status, as defined by the original query. )
Benefits of Using Views
- Simplification: Complex queries can be stored as views and reused.
- Security: Restrict access to specific columns or rows.
- Consistency: Centralize logic that would otherwise be duplicated across queries.
- Logical Separation: Allows abstraction from actual table structure.
- Updating a View:- Use CREATE OR REPLACE to update an existing view without dropping it.
Syntax:-
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
Example:-
CREATE OR REPLACE VIEW active_customers AS
SELECT id, name FROM customers WHERE status = ‘active’;
- Dropping a View:- To delete an existing view:
Syntax:-
DROP VIEW view_name;
Example:
DROP VIEW active_customers;
- Updatable Views
A view is considered updatable if:
- It references a single base table.
- It does not contain aggregate functions (SUM(), AVG(), etc.), DISTINCT, GROUP BY, HAVING, UNION, or subqueries in the SELECT list.
- It does not include LIMIT or OFFSET.
If these conditions are met, you can perform INSERT, UPDATE, or DELETE on the view, and changes will reflect in the base table.
- Deleting from a View:- This will delete the corresponding row from the customers table if the view is updatable.
Syntax:-
DELETE FROM table_name
WHERE condition;
Example:-
DELETE FROM active_customers WHERE id = 5;
- WITH CHECK OPTION:- The WITH CHECK OPTION ensures that all updates or inserts through the view meet the view’s condition.
Syntax:
CREATE VIEW view_name AS
SELECT … FROM … WHERE condition
WITH CHECK OPTION;
Example:
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = ‘active’
WITH CHECK OPTION;
In this case, updates that change status to anything other than ‘active’ will be rejected when done through the view.
- Non-Updatable Views
Views containing the following are not updatable:
- GROUP BY
- DISTINCT
- Aggregate functions (SUM, AVG, etc.)
- JOIN across multiple tables
- UNION
- LIMIT or OFFSET
- Subqueries in the select list
Attempting to update or delete through such views will result in an error.
- Checking View Definition
General Syntax to view the SQL statement behind a view:
SHOW CREATE VIEW view_name;
Example:
SHOW CREATE VIEW active_customers;