Creating Views in MySQL

A view is a named query stored in the database that behaves like a virtual table. It does not store data itself; each time you query a view, MySQL executes the underlying SELECT statement. Views simplify complex queries, provide a layer of abstraction, and can restrict which columns or rows users see.

Creating a View

Use CREATE VIEW followed by the view name and the SELECT statement that defines it. Once created, you query the view exactly like a table.

CREATE VIEW active_employees AS
SELECT id, first_name, last_name, email, department
FROM employees
WHERE status = 'active';

-- Query the view
SELECT * FROM active_employees
WHERE department = 'Engineering';

Views for Complex Joins

When a query involves several joins and is used in multiple places, a view keeps your code DRY and easier to maintain. If the underlying logic changes, you update the view in one place.

CREATE VIEW order_summary AS
SELECT
  o.order_id,
  c.first_name,
  c.last_name,
  o.order_date,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN customers c    ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.order_id, c.first_name, c.last_name, o.order_date;

-- Use it simply
SELECT * FROM order_summary
WHERE order_total > 500
ORDER BY order_date DESC;

Replacing a View

CREATE OR REPLACE VIEW lets you modify an existing view without dropping it first. This is the preferred way to update view definitions.

CREATE OR REPLACE VIEW active_employees AS
SELECT id, first_name, last_name, email, department, hire_date
FROM employees
WHERE status = 'active'
  AND hire_date >= '2020-01-01';

Updatable Views

Some views are updatable, meaning you can run INSERT, UPDATE, or DELETE on them and the changes pass through to the underlying table. A view is updatable when it maps directly to a single table without aggregations, DISTINCT, GROUP BY, UNION, or subqueries.

-- This simple view is updatable
CREATE VIEW engineering_team AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Engineering';

-- Update through the view
UPDATE engineering_team
SET salary = salary * 1.05
WHERE id = 10;

Dropping a View

Remove a view with DROP VIEW. This does not affect the underlying data in any way.

DROP VIEW IF EXISTS active_employees;

Best Practices

Use views to encapsulate business logic, restrict sensitive columns, and simplify application queries. Avoid deeply nested views (views built on views) because they can become hard to debug and may hurt performance. Always check the execution plan with EXPLAIN when querying views on large tables.