MySQL Table Vs View

Last updated: October 19, 2026Oct 19, 2026 5 min read

Tables and views look the same when you query them: both have columns and return rows. Underneath, they are very different. A table stores data. A view stores a query. This page compares them side by side with examples, so you know which to use and when.

1. The Core Difference

2. Same Question, Table vs View

Let's answer "total paid per customer" both ways. First as a view:

CREATE VIEW paid_totals_v AS
SELECT customer, SUM(amount) AS total_paid
FROM orders WHERE status = 'paid'
GROUP BY customer;

SELECT * FROM paid_totals_v ORDER BY total_paid DESC;

Result: 2 rows

Then as a table (a snapshot of the same result):

CREATE TABLE paid_totals_t AS
SELECT customer, SUM(amount) AS total_paid
FROM orders WHERE status = 'paid'
GROUP BY customer;

SELECT * FROM paid_totals_t ORDER BY total_paid DESC;

Result: 2 rows

3. Freshness: Watch Them Diverge

Now Sahil's pending order is paid:

UPDATE orders SET status = 'paid' WHERE order_id = 2;

SELECT 'view'  AS source, customer, total_paid FROM paid_totals_v WHERE customer = 'Sahil'
UNION ALL
SELECT 'table' AS source, customer, total_paid FROM paid_totals_t WHERE customer = 'Sahil';

Result: 2 rows

The view picked up the change immediately; the table still shows the old total until you rebuild or update it.

4. Storage

SELECT table_name, table_type, data_length
FROM information_schema.tables
WHERE table_schema = 'store'
ORDER BY table_type, table_name;

Result: 3 rows

The view has no data length at all (NULL); the tables have allocated pages.

5. Changing Data

You can always change a table. An aggregate view can't be changed, because there's no single row to update:

UPDATE paid_totals_v SET total_paid = 0 WHERE customer = 'Ritu';

ERROR 1288 (HY000): The target table paid_totals_v of the UPDATE is not updatable

A simple view on one table can be updated, and the change goes to the base table:

CREATE VIEW pending_orders AS SELECT order_id, status FROM orders WHERE status = 'pending';
INSERT INTO orders VALUES (6, 'Ritu', 700, 'pending', '2026-03-04');
UPDATE pending_orders SET status = 'paid' WHERE order_id = 6;
SELECT order_id, status FROM orders WHERE order_id = 6;

Result: 1 row

6. Performance

• A view adds no speed by itself: querying it runs the underlying SELECT each time.
• With the MERGE algorithm, MySQL folds the view into your query, so indexes on the base tables still help.
• A summary table answers instantly but needs refreshing (on a schedule, with an event, or by triggers).
• MySQL has no materialized views; a summary table plus a scheduled refresh is the usual substitute.

-- refresh the snapshot table on demand
TRUNCATE TABLE paid_totals_t;
INSERT INTO paid_totals_t
SELECT customer, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY customer;
SELECT * FROM paid_totals_t ORDER BY customer;

Result: 2 rows

7. Security

Both can be granted to users, but a view can expose part of a table. Granting SELECT on a view of only customer and status keeps amount private, without copying data into a separate table that must be kept in sync.

8. When to Use Which

9. Practice Exercises

CREATE VIEW daily_sales AS
SELECT ordered_on, SUM(amount) AS total
FROM orders WHERE status = 'paid'
GROUP BY ordered_on;
SELECT * FROM daily_sales ORDER BY ordered_on;

SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = 'store';

10. Summary

• A table stores data; a view stores a SELECT and reads data when queried.
• Views are always current and use no data space; tables keep whatever was written.
• Tables support indexes, constraints and triggers; only simple views are updatable.
• Use views to simplify and secure; use (summary) tables when you need stored or pre-computed data.