What is the difference between a View and a Table?
Difference Between a View and a Table in MySQL
A View and a Table both look similar when queried, but they differ fundamentally in storage, behavior, and purpose in MySQL.
- 1
A Table stores physical data on disk, while a View stores only a SQL query definition.
- 2
Tables hold actual rows of data; Views are virtual tables generated dynamically when queried.
- 3
Updating data is always possible in tables, but only certain types of views are updatable.
- 4
Tables consume storage space; Views consume almost no storage (only metadata).
- 5
Views reflect real-time data from the underlying tables.
- 6
Tables can have indexes; Views cannot have indexes (except indexed generated columns in some cases).
- 1
To store persistent application data.
- 2
When indexing and performance optimization are required.
- 3
When you need full CRUD capabilities without restrictions.
- 1
To simplify complex SQL queries (joins, filters).
- 2
To restrict access to sensitive columns.
- 3
To present a consistent interface even when table structures change.
- 4
To build reusable reporting or analytical query layers.
In summary, a table stores physical data, while a view provides a virtual, query-based representation of data—useful for simplifying queries, enhancing security, and presenting formatted datasets.
Scenario Questions
0-2 years experience
- 1If you need to create a read‑only report that joins three tables, would you use a view or a table, and why?
- 2What happens when you try to INSERT into a view that selects from multiple base tables?
- 3How would you refresh a view after the underlying tables have changed?
2-5 years experience
- 1We added a new column to a base table, but a view used in a reporting service started throwing errors. Walk me through how you'd debug this.
- 2When would you choose a materialized view or a scheduled table over a regular view for a feature that needs near‑real‑time data?
- 3Explain the performance implications of using a view that contains a complex subquery versus creating a denormalized table.
5-8 years experience
- 1Our analytics pipeline runs millions of queries against a view that aggregates daily sales. Latency is growing. How would you redesign this component?
- 2Discuss the trade‑offs of replacing a set of heavily used views with pre‑computed tables in a high‑traffic e‑commerce site.
- 3How do MySQL's view definition storage and execution affect query planning and caching at scale?
8+ years experience
- 1We have a legacy system that relies on dozens of nested views across multiple services. We're planning a migration to a micro‑service architecture. How would you approach refactoring or replacing these views to ensure data consistency and maintainability?
- 2Consider a multi‑tenant SaaS platform where each tenant can define custom views. What architectural patterns would you use to support this while keeping the schema manageable?
- 3What are the long‑term maintenance implications of using views versus materialized tables for audit logging across the organization?
Follow-up Questions
- Can you give an example where a view would cause a performance bottleneck?
- How would you handle write operations through a view?
- What strategies exist to materialize a view's result in MySQL?