Eric Campbell 5 min read MySQL

How to Use Views in MySQL

Views are stored SQL queries that behave like virtual tables. Unlike physical tables, views don't store data themselves—they dynamically generate results by executing the underlying SELECT statement...

Key Insights

  • Views are virtual tables that simplify complex queries, improve security by restricting column access, and provide a consistent interface when underlying table structures change
  • Most views in MySQL are non-materialized, meaning they execute the underlying query each time they’re accessed, which can impact performance for complex operations
  • Views are updatable only when they meet specific criteria: no aggregates, no DISTINCT, no GROUP BY, and a direct one-to-one mapping to underlying tables

Understanding MySQL Views

Views are stored SQL queries that behave like virtual tables. Unlike physical tables, views don’t store data themselves—they dynamically generate results by executing the underlying SELECT statement each time you query them. This makes views powerful tools for simplifying complex queries, enforcing security policies, and creating logical abstractions over your database schema.

You should use views when you need to repeatedly execute complex queries, want to restrict access to specific columns or rows, or need to maintain backward compatibility when refactoring table structures. Views are particularly valuable in applications with multiple consumers of the same data, where centralizing query logic prevents inconsistencies.

Here’s the basic syntax for creating a view:

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

Creating Basic Views

Let’s start with practical examples using a sample database. Assume we have an employees table with columns: id, first_name, last_name, email, salary, department_id, and hire_date.

Creating a simple view that shows only active employees:

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

Now you can query this view like any table:

SELECT * FROM active_employees WHERE department_id = 5;

Views excel at hiding complexity. Here’s a view that creates calculated columns:

CREATE VIEW employee_summary AS
SELECT 
    id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    email,
    salary,
    ROUND(salary * 1.15, 2) AS projected_salary,
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_employed
FROM employees;

This view combines first and last names, calculates a projected salary increase, and computes tenure—all without modifying the underlying table or writing this logic repeatedly in your application code.

You can also filter sensitive data. This view excludes salary information:

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

Grant access to this view instead of the base table, and users can’t see salary data even if they try.

Working with Complex Views

Views become truly powerful when dealing with multi-table queries. Consider a database with employees, departments, and salaries tables:

CREATE VIEW employee_department_details AS
SELECT 
    e.id,
    e.first_name,
    e.last_name,
    e.email,
    d.department_name,
    d.location,
    e.hire_date
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.status = 'active';

For reporting and analytics, aggregate views are invaluable:

CREATE VIEW department_statistics AS
SELECT 
    d.department_name,
    COUNT(e.id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MIN(e.salary) AS min_salary,
    MAX(e.salary) AS max_salary,
    SUM(e.salary) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.status = 'active'
GROUP BY d.id, d.department_name;

Views can also incorporate subqueries for sophisticated logic:

CREATE VIEW high_performers AS
SELECT 
    e.id,
    e.first_name,
    e.last_name,
    e.salary,
    e.department_id,
    (SELECT AVG(salary) 
     FROM employees 
     WHERE department_id = e.department_id) AS dept_avg_salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id
);

This view identifies employees earning above their department’s average without requiring complex joins in your application queries.

Modifying and Managing Views

When you need to change a view’s definition, use CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW active_employees AS
SELECT 
    id, 
    first_name, 
    last_name, 
    email, 
    department_id,
    hire_date  -- Added column
FROM employees
WHERE status = 'active';

This approach is safer than dropping and recreating because it preserves grants and dependencies. However, MySQL also supports ALTER VIEW for modifying the SELECT statement:

ALTER VIEW active_employees AS
SELECT id, first_name, last_name, email, department_id, hire_date, salary
FROM employees
WHERE status = 'active';

To inspect a view’s definition:

SHOW CREATE VIEW active_employees;

For programmatic access to view metadata:

SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    CHECK_OPTION,
    IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'your_database_name';

This query is particularly useful when documenting your database or building automated tools that need to understand your view structure.

Updatable Views

MySQL allows you to perform INSERT, UPDATE, and DELETE operations through views, but only when specific conditions are met. The view must:

  • Not contain aggregate functions (SUM, COUNT, AVG, etc.)
  • Not use DISTINCT, GROUP BY, or HAVING
  • Not use UNION or subqueries in the FROM clause
  • Have a one-to-one relationship with underlying tables

Here’s an updatable view:

CREATE VIEW editable_employees AS
SELECT id, first_name, last_name, email, department_id
FROM employees
WHERE department_id = 5;

-- This works
UPDATE editable_employees 
SET email = 'newemail@company.com' 
WHERE id = 100;

-- This also works
INSERT INTO editable_employees (first_name, last_name, email, department_id)
VALUES ('John', 'Doe', 'john@company.com', 5);

The WITH CHECK OPTION clause prevents updates that would make rows disappear from the view:

CREATE VIEW department_five_employees AS
SELECT id, first_name, last_name, email, department_id
FROM employees
WHERE department_id = 5
WITH CHECK OPTION;

-- This fails because it violates the WHERE clause
UPDATE department_five_employees 
SET department_id = 3 
WHERE id = 100;

Non-updatable views contain aggregates or joins:

CREATE VIEW department_stats AS
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- This fails - can't update an aggregate view
UPDATE department_stats SET employee_count = 10 WHERE department_id = 5;

Performance Considerations and Best Practices

Views don’t cache results—MySQL executes the underlying query every time you access the view. For complex views with joins and aggregates, this can impact performance:

-- Check the execution plan
EXPLAIN SELECT * FROM department_statistics WHERE department_name = 'Engineering';

Compare this with the execution plan of the underlying query. If the view performs poorly, consider:

  1. Adding indexes to underlying tables on columns used in WHERE, JOIN, and GROUP BY clauses
  2. Simplifying the view by removing unnecessary columns or complex calculations
  3. Using materialized alternatives like summary tables updated via triggers or scheduled jobs

For frequently accessed aggregate data, create a physical summary table instead:

CREATE TABLE department_statistics_cache AS
SELECT 
    d.department_name,
    COUNT(e.id) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name;

-- Refresh periodically
TRUNCATE department_statistics_cache;
INSERT INTO department_statistics_cache
SELECT /* your aggregate query */;

Follow these naming conventions: prefix views with v_ or use a _view suffix to distinguish them from tables. Document complex views with comments:

CREATE VIEW v_employee_compensation AS
-- Combines employee data with current salary and bonus information
-- Used by: HR Dashboard, Payroll System, Annual Reports
-- Updated: 2024-01-15
-- Owner: HR Team
SELECT /* columns */;

Common Use Cases and Patterns

Dashboard Aggregations: Create views that pre-join and aggregate data for reporting dashboards:

CREATE VIEW v_sales_dashboard AS
SELECT 
    DATE(order_date) AS sale_date,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(order_total) AS revenue,
    AVG(order_total) AS avg_order_value
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(order_date);

Security and Privacy: Hide sensitive columns while maintaining data access:

CREATE VIEW v_customer_public AS
SELECT 
    customer_id,
    first_name,
    last_name,
    city,
    state,
    -- Exclude: SSN, credit_card, full address
    created_at
FROM customers;

GRANT SELECT ON v_customer_public TO 'reporting_user'@'%';

API Data Layer: Create views that match your API response structure, decoupling your database schema from external contracts:

CREATE VIEW v_api_user_profile AS
SELECT 
    u.id AS userId,
    u.username,
    u.email,
    p.avatar_url AS avatarUrl,
    p.bio,
    COUNT(DISTINCT f.follower_id) AS followerCount
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
LEFT JOIN followers f ON u.id = f.following_id
GROUP BY u.id, u.username, u.email, p.avatar_url, p.bio;

Views are essential tools for building maintainable database architectures. Use them to simplify complex queries, enforce security boundaries, and create stable interfaces over evolving schemas. Just remember to monitor performance and consider materialized alternatives for heavy aggregations.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.