What are VIEW and materialized VIEW used for in SQL?

A VIEW in SQL is a virtual table that represents a stored query. It does not physically store data but executes a specific SELECT when accessed, simplifying complex queries and improving code readability.

Materialized VIEWS (materialized views) are tables that physically store the result of a query. They are used to speed up data access, especially if queries are complex and resource-intensive, but the data is not updated frequently.

Example:

-- Creating a regular VIEW
CREATE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';

-- Creating a materialized VIEW (syntax depends on the DBMS)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Thus, VIEWS are convenient for abstraction and reusing queries, while materialized VIEWS are used to enhance performance when working with large volumes of data.