MYSQL Tutorial

MySQL VIEWS

A VIEW is a virtual table defined by a SQL query. It encapsulates complex joins and filters, making queries simpler and reusable.

Creating a View

CREATE VIEW active_customers AS
SELECT customer_id, customer_name, last_order_date
FROM customers
WHERE status = 'active';
  

Now you can query active_customers like a table: SELECT * FROM active_customers;

View with Joins & Aggregates

CREATE VIEW dept_sales AS
SELECT 
  d.department_name,
  COUNT(o.order_id) AS orders_count,
  SUM(o.amount)      AS total_sales
FROM departments AS d
LEFT JOIN orders AS o
  ON d.department_id = o.department_id
GROUP BY d.department_name;
  

Updating a View

To modify its definition, use CREATE OR REPLACE:

CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, customer_name, email, last_order_date
FROM customers
WHERE status = 'active';
  

Dropping a View

DROP VIEW IF EXISTS active_customers;
  

Key Tips & Best Practices

• Use views to simplify complex queries and enforce consistent filters.
• Avoid views with non-deterministic functions for updatable views.
• Grant permissions on views to control data access.
• Be mindful of performance: deep nested views can be slower.
• Use EXPLAIN to check how MySQL optimizes view queries.