What is the difference between view and materialized view?

In SQL databases, both views and materialized views are derived tables used to simplify data access and enhance security. However, they differ significantly in how they store and manage data, impacting their performance characteristics and use cases.

SQL View

A SQL view is a virtual table based on the result-set of a SQL query. It does not store data physically; instead, it stores the query definition itself. When a view is queried, the underlying query is executed, and the results are presented dynamically. This means views always reflect the most current data in the base tables.

Views are primarily used for simplifying complex queries, abstracting data, enforcing security by restricting access to specific rows or columns, and providing a consistent interface to data even if the underlying schema changes.

CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 70000;

Materialized View

A materialized view, unlike a regular view, is a database object that stores the pre-computed results of a query as a physical table. When you create a materialized view, the database executes the query and stores the output in a dedicated segment on disk. This pre-computation makes querying a materialized view much faster than executing the original complex query every time.

Materialized views are particularly useful for optimizing the performance of complex analytical queries, reporting, and data warehousing environments where data freshness is less critical than query speed. They require a refresh mechanism to update their stored data when changes occur in the base tables.

CREATE MATERIALIZED VIEW daily_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT sale_date, SUM(amount) AS total_sales
FROM orders
GROUP BY sale_date;

Key Differences