Views — MySQL | LetCodes

MySQL Views

A view is a saved SQL query that behaves like a virtual table. You query it exactly like a real table, but the data is computed on the fly from the underlying base tables. Views are one of the most powerful tools for simplifying complex queries, enforcing security, and creating stable interfaces between application layers.

Creating a View

The basic syntax uses CREATE VIEW:

SQL

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

-- Real example: active customers
CREATE VIEW active_customers AS
SELECT
  customer_id,
  first_name,
  last_name,
  email,
  created_at
FROM customers
WHERE status = 'active' AND deleted_at IS NULL;

Once created, query the view exactly like a table:

SQL

SELECT * FROM active_customers;
SELECT email FROM active_customers WHERE last_name = 'Smith';

Note

CREATE VIEW does not store data — it stores the query definition. Every time you SELECT from a view, MySQL executes the underlying query against the current data.

OR REPLACE

SQL

-- Replace existing view definition without DROP + CREATE
CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone, created_at
FROM customers
WHERE status = 'active' AND deleted_at IS NULL;

-- Monthly revenue view
CREATE OR REPLACE VIEW monthly_revenue AS
SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(total_amount)                AS revenue,
  COUNT(*)                         AS order_count
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

Views with JOINs

Views are especially useful for hiding complex multi-table joins:

SQL

CREATE OR REPLACE VIEW order_details_view AS
SELECT
  o.order_id,
  o.order_date,
  o.status,
  c.first_name,
  c.last_name,
  c.email,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  (oi.quantity * oi.unit_price) AS line_total
FROM orders         o
JOIN customers      c  ON o.customer_id = c.customer_id
JOIN order_items    oi ON o.order_id    = oi.order_id
JOIN products       p  ON oi.product_id = p.product_id;

-- Application code stays clean
SELECT * FROM order_details_view WHERE order_id = 1042;

Updatable vs Read-Only Views

MySQL allows INSERT, UPDATE, and DELETE through a view — but only if the view is updatable. A view is updatable when:

It references exactly one base table
It does not use DISTINCT
It does not use aggregate functions (SUM, COUNT, AVG, etc.)
It does not use GROUP BY or HAVING
It does not use UNION or UNION ALL
It does not use subqueries in the SELECT list

SQL

-- This view IS updatable (single table, no aggregates)
CREATE VIEW simple_customers AS
SELECT customer_id, first_name, last_name, email, status
FROM customers;

-- UPDATE through the view modifies the base table
UPDATE simple_customers
SET status = 'inactive'
WHERE customer_id = 7;

-- INSERT through the view inserts into the base table
INSERT INTO simple_customers (first_name, last_name, email, status)
VALUES ('Jane', 'Doe', 'jane@example.com', 'active');

-- This view is NOT updatable (uses GROUP BY + aggregate)
CREATE VIEW revenue_by_month AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) AS total
FROM orders
GROUP BY month;

Warning

Updating through a view only affects columns visible in the view. Columns in the base table that are NOT in the view keep their existing values (or use DEFAULT on INSERT).

WITH CHECK OPTION

WITH CHECK OPTION prevents INSERT/UPDATE through a view from creating rows that would fall outside the view's WHERE clause:

SQL

CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, status
FROM customers
WHERE status = 'active'
WITH CHECK OPTION;

-- This INSERT succeeds — satisfies WHERE status = 'active'
INSERT INTO active_customers (first_name, last_name, email, status)
VALUES ('Bob', 'Jones', 'bob@example.com', 'active');

-- This UPDATE fails — row would become invisible through the view
UPDATE active_customers
SET status = 'inactive'
WHERE customer_id = 3;
-- ERROR 1369: CHECK OPTION failed 'mydb.active_customers'

-- LOCAL vs CASCADED for nested views
CREATE VIEW high_value_orders AS
SELECT * FROM orders WHERE total_amount > 100;

-- LOCAL: only checks this view's WHERE clause
CREATE VIEW recent_high_value AS
SELECT * FROM high_value_orders
WHERE order_date >= '2024-01-01'
WITH LOCAL CHECK OPTION;

-- CASCADED (default): checks all ancestor view WHERE clauses too
CREATE VIEW recent_high_value_strict AS
SELECT * FROM high_value_orders
WHERE order_date >= '2024-01-01'
WITH CASCADED CHECK OPTION;

Inspecting View Definitions

SQL

-- Show the CREATE VIEW statement
SHOW CREATE VIEW active_customersG

-- List all views in current database
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Query information_schema for view metadata
SELECT
  TABLE_NAME       AS view_name,
  IS_UPDATABLE,
  SECURITY_TYPE,
  CHARACTER_SET_CLIENT
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

ALTER and DROP Views

SQL

-- ALTER VIEW replaces the definition
ALTER VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone, status, created_at
FROM customers
WHERE status = 'active' AND deleted_at IS NULL;

-- Drop a single view
DROP VIEW active_customers;

-- Drop if it exists (avoids error when view may not exist)
DROP VIEW IF EXISTS active_customers;

-- Drop multiple views at once
DROP VIEW IF EXISTS active_customers, order_details_view, monthly_revenue;

View Performance: MERGE vs TEMPTABLE

MySQL does NOT cache view results by default. It uses one of two execution algorithms:

Algorithm	Behaviour	When Used
MERGE	View SQL is merged into outer query — one combined query executes	Simple views: no GROUP BY, DISTINCT, or aggregates
TEMPTABLE	View result materializes into a temp table first, then the outer query runs on it	Views with GROUP BY, DISTINCT, aggregates, or UNION

SQL

-- Force MERGE algorithm (MySQL may override if not possible)
CREATE ALGORITHM = MERGE VIEW fast_view AS
SELECT customer_id, email FROM customers WHERE status = 'active';

-- Check the algorithm MySQL chose
EXPLAIN SELECT * FROM active_customers WHERE last_name = 'Smith'G

Tip

Views using TEMPTABLE cannot be updated and prevent index pushdown. If performance is critical, consider a real table with scheduled refresh, a CTE, or a refactored query instead.

Views for Security — Column-Level Access

Views let you expose only specific columns to a database user, hiding sensitive data like salaries, passwords, or PII:

SQL

-- employees table has salary, ssn, password_hash columns
-- Expose only safe columns to the reporting role
CREATE VIEW employees_public AS
SELECT
  employee_id,
  first_name,
  last_name,
  department,
  job_title,
  hire_date
FROM employees;

-- Grant SELECT on the VIEW only — never on the base table
GRANT SELECT ON mydb.employees_public TO 'reporting_user'@'%';

-- The reporting user sees employee info but never salary or SSN

SQL SECURITY: DEFINER vs INVOKER

SQL

-- DEFINER (default): view runs with the creator's privileges
CREATE DEFINER = 'admin'@'localhost'
SQL SECURITY DEFINER
VIEW admin_view AS
SELECT * FROM sensitive_table;

-- INVOKER: view runs with the calling user's privileges
CREATE SQL SECURITY INVOKER
VIEW invoker_view AS
SELECT * FROM sensitive_table;
-- If the calling user lacks SELECT on sensitive_table, query errors

Replacing Complex Subqueries with Views

SQL

-- Before: nested subquery that appears in many application queries
SELECT customer_id, total_spent
FROM (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY o.customer_id
) AS spending
WHERE total_spent > 1000;

-- Step 1: Extract into a named view
CREATE VIEW customer_lifetime_value AS
SELECT
  o.customer_id,
  SUM(oi.quantity * oi.unit_price) AS total_spent,
  COUNT(DISTINCT o.order_id)       AS total_orders,
  MIN(o.order_date)                AS first_order,
  MAX(o.order_date)                AS last_order
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id;

-- Step 2: All queries are now clean and consistent
SELECT c.first_name, c.last_name, clv.total_spent, clv.total_orders
FROM customers c
JOIN customer_lifetime_value clv ON c.customer_id = clv.customer_id
WHERE clv.total_spent > 1000
ORDER BY clv.total_spent DESC;

Practical Example: Sales Dashboard View

SQL

CREATE OR REPLACE VIEW sales_dashboard AS
SELECT
  DATE_FORMAT(o.order_date, '%Y-%m')   AS month,
  COUNT(DISTINCT o.order_id)           AS total_orders,
  COUNT(DISTINCT o.customer_id)        AS unique_customers,
  SUM(oi.quantity * oi.unit_price)     AS gross_revenue,
  AVG(oi.quantity * oi.unit_price)     AS avg_order_value,
  SUM(CASE WHEN o.status = 'returned'
        THEN oi.quantity * oi.unit_price
        ELSE 0 END)                    AS returns_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month DESC;

-- Dashboard query is now a one-liner
SELECT * FROM sales_dashboard LIMIT 12;

View Limitations

Views cannot have their own indexes — only base table indexes are used
Views cannot use temporary tables as their base
TEMPTABLE views re-execute the full aggregation on every query
Circular view references are not allowed
ORDER BY inside a view is ignored when the outer query provides its own ORDER BY
Views do not support stored procedure calls inside them

Best Practices

Use a naming convention like _view or _v suffix to distinguish views from real tables
Use views to create stable API contracts — app code queries the view while the schema can evolve underneath
Prefer MERGE-compatible views (no GROUP BY or aggregates) for index-efficient queries
Add WITH CHECK OPTION on writeable views to prevent silent data drift
Audit views periodically — orphaned views referencing dropped tables produce cryptic runtime errors
For very heavy aggregation views, consider a summary table refreshed by a scheduled event instead