What is a SQL View and when should you use standard vs materialized views?

Explanation & Concept

A View is a virtual table defined by a stored SELECT query. It contains no stored data itself; every time a view is queried, the database executes the underlying query.

• Standard Views simplify complex queries, encapsulate business logic, and restrict column access for security. • Materialized Views physically persist the query result to disk and can be indexed, delivering high read performance for expensive analytical queries at the expense of needing periodic refreshes.

Code Example

sql

-- Create a standard view for active customer summaries
CREATE VIEW v_active_customer_orders AS
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE c.is_active = TRUE
GROUP BY c.customer_id, c.name;

-- Query the view just like a regular table
SELECT * FROM v_active_customer_orders WHERE lifetime_value > 1000;

How It Works & Key Takeaways

Applications can query `v_active_customer_orders` directly without writing complex multi-table joins repeatedly, standardizing the definition of lifetime value across all reports.

ViewsIntermediate

1Question 1 of 2

sql

What is a SQL View and when should you use standard vs materialized views?

Explanation & Concept

A View is a virtual table defined by a stored SELECT query. It contains no stored data itself; every time a view is queried, the database executes the underlying query.

Code Example

sql

-- Create a standard view for active customer summaries
CREATE VIEW v_active_customer_orders AS
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE c.is_active = TRUE
GROUP BY c.customer_id, c.name;

-- Query the view just like a regular table
SELECT * FROM v_active_customer_orders WHERE lifetime_value > 1000;

How It Works & Key Takeaways

Applications can query `v_active_customer_orders` directly without writing complex multi-table joins repeatedly, standardizing the definition of lifetime value across all reports.