In relational databases, what is the difference between a View and a Materialized View?

Introduction / Context:
Views and materialized views are powerful features in relational database systems such as Oracle, PostgreSQL and others. They allow developers to encapsulate complex queries, improve security and sometimes enhance performance. Interviews often ask for the difference between a normal view and a materialized view to check whether candidates understand virtual versus physically stored query results and the impact on performance and storage.

Given Data / Assumptions:

• A normal view is defined by a SELECT query over one or more base tables.
• A materialized view is also defined by a query, but it can store the result set on disk.
• Both objects can be queried as if they were tables.
• We are concerned with how data is stored, refreshed and retrieved.

Concept / Approach:
A standard view is a virtual table that does not store data by itself. Instead, the database saves only the view definition. When a query references the view, the database engine rewrites that query using the underlying SELECT statement and accesses the base tables in real time. A materialized view, by contrast, stores the result of the defining query physically, often with additional indexes. Queries against a materialized view can be faster because they read precomputed data. However, the materialized view can become stale, so it needs to be refreshed either on demand or according to a schedule to keep it in sync with the underlying tables.

Step-by-Step Solution:
Step 1: Define a normal view as CREATE VIEW v AS SELECT ... FROM base tables. The database records this query definition but not the result rows. Step 2: When a user runs SELECT from v, the database engine expands v into its underlying query and fetches data from the base tables at that moment. Step 3: Define a materialized view as CREATE MATERIALIZED VIEW mv AS SELECT ... FROM base tables. Here, the database executes the query and stores the resulting rows on disk. Step 4: Subsequent queries on mv read from these stored rows, which can dramatically reduce computation cost for complex aggregations or joins. Step 5: Recognise that because base tables can change, the materialized view needs a refresh mechanism to update its stored data, whereas a normal view always shows current data because it is computed on demand.

Verification / Alternative check:
Imagine a reporting query that aggregates millions of sales records by region and month. A normal view encapsulating this query still has to compute the aggregation each time it is used, which can be slow. A materialized view precomputes and stores the summary, so queries against it can be very fast. However, if new sales records are inserted, the materialized view must be refreshed to include them, while the normal view would show them immediately. This real world behaviour matches the description in option A, distinguishing virtual views from physically stored materialized views.

Why Other Options Are Wrong:
Option B reverses the storage behaviour, incorrectly stating that a view stores data while a materialized view is always dynamic. Option C says a materialized view can never have indexes, but many systems allow indexing materialized views to speed up queries further. Option D describes a materialized view as only a security feature with no data or definition, which is not accurate. Option E claims there is no difference between the two, ignoring the key distinction between virtual and physically stored query results. Only option A correctly explains that a view is virtual and computed on demand, while a materialized view stores data physically and can be refreshed.

Common Pitfalls:
A common misconception is that materialized views are always better because they are faster. In reality, they require extra storage and maintenance effort to keep them updated. Another pitfall is assuming that normal views automatically improve performance; in many cases, they mainly improve query readability and security rather than speed. In interviews, emphasise that views store only definitions and always read current data, while materialized views store precomputed data and trade freshness for performance, which may or may not be appropriate depending on the use case.

Final Answer:
A View is a virtual table that stores only the query definition and is computed on demand, whereas a Materialized View stores the query result physically and can be refreshed periodically.