SQL View
A view in SQL is essentially a virtual table. It doesn’t store data physically. Instead it presents data from one or more underlying tables through a predefined SQL query. Think of it as a saved query that you can treat like a table.
- Views don’t hold data themselves. When you query a view, the database executes the underlying query to fetch data in real-time.
- Views can simplify complex queries by encapsulating them. Instead of writing a complex JOIN or subquery each time, you select from the view.
- Views can restrict user access to specific rows or columns, enhancing security by exposing only necessary data.
- Since views are generated on the fly, they always reflect the current state.
CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE status = 'active';
When to use a view
- Use it to simplify complex queries that you use frequently and you need the most current data every time.
- Restrict user access to specific data by exposing only certain columns or rows through a view
Materialized View
A materialized view is like a regular view, but it stores the query result’s phisically and it doesn’t involve executing the underlying query each time.
- Since data is precomputed and stored, querying a materialized view is faster for complex queries over large datasets.
- Because of this, data in a materialized view can become outdated and needs to be refreshed periodically.
CREATE OR REPLACE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;
When to use a materialized view
- Is ideal for speeding up complex queries that are resource-intensive and slow to execute.
- Suitable for scenarios where data doesn’t change frequently and fast read performance is needed.
- You need to tolerate data that’s not always up-to-date