How to CREATE VIEW in MySQL

Why create a view instead of a table?

Views hide complex joins, standardize business logic, and improve security by exposing only selected columns. Because they store no data, views save space and always show the latest underlying table data.

What is the basic CREATE VIEW syntax?

Use CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED|LOCAL] CHECK OPTION]. Key options control replacement behaviour, execution plan, and update rules.

How do I build a simple ecommerce view?

The example below creates customer_orders_v, joining Customers and Orders so analysts no longer write repetitive joins.

CREATE VIEW customer_orders_v AS
SELECT c.id AS customer_id,
c.name AS customer_name,
o.id AS order_id,
o.order_date,
o.total_amount
FROM Customers c
JOIN Orders o ON o.customer_id = c.id;

How can I update or recreate an existing view?

Add OR REPLACE to overwrite the old definition without dropping and re-granting permissions.

CREATE OR REPLACE VIEW customer_orders_v AS
SELECT c.id , c.name , COUNT(o.id) AS order_cnt
FROM Customers c
LEFT JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id , c.name;

Can I control the execution algorithm?

Specify ALGORITHM=MERGE for simple SELECTs so MySQL inlines the view, or TEMPTABLE when the query uses GROUP BY or DISTINCT to avoid problems such as “view merge failed.”

How do I secure sensitive columns?

Create views that expose only approved fields and grant users SELECT on the view, not on the base tables. Combine with DEFINER to run the view with a privileged account.

What are best practices for performance?

Keep view definitions simple, index join columns on base tables, and test EXPLAIN on SELECT * FROM view. For heavy aggregations, consider materialized tables updated on a schedule.

Common mistakes and fixes

Using SELECT *

SELECT * locks the column list, so later table alterations break the view. List columns explicitly to avoid “view’s SELECT contains no column names” errors.

Naming conflicts

Alias columns in the SELECT so each name is unique; otherwise MySQL returns “duplicate column name” at creation time.

Quick reference

• Replace safely: CREATE OR REPLACE VIEW v AS ...
• Inline execution: ALGORITHM=MERGE
• Restrict updates: WITH CASCADED CHECK OPTION
• Drop: DROP VIEW IF EXISTS v;

FAQs

Does MySQL cache view results?

No. Each SELECT reads base tables, so add indexes or materialize if speed is critical.

Can I insert into a view?

Yes, if the view maps directly to one table and has no aggregates or DISTINCT. Use WITH CHECK OPTION to validate data.

How do I list all views?

Query INFORMATION_SCHEMA.VIEWS or run SHOW FULL TABLES WHERE Table_type = 'VIEW';