view

Definition

A view is a virtual table based on the result of a SQL query. Unlike physical tables that store data directly, a view acts as a stored query that appears as a table when referenced. Views provide a way to simplify complex queries, restrict access to underlying data, and create logical abstractions of your data model.

Usage in DuckDB

DuckDB supports creating views using the CREATE VIEW syntax. Views in DuckDB are not materialized - they run their underlying query each time they are accessed. Here are some examples:

Basic view creation:

Copy code
CREATE VIEW monthly_sales AS
SELECT 
    date_trunc('month', sale_date) as month,
    sum(amount) as total_sales
FROM sales
GROUP BY 1;

View with column aliases:

Copy code
CREATE VIEW customer_summary (customer_name, total_spent, order_count) AS
SELECT 
    name,
    sum(order_total),
    count(*)
FROM orders
GROUP BY name;

Views can also reference other views:

Copy code
CREATE VIEW high_value_customers AS
SELECT *
FROM customer_summary
WHERE total_spent > 10000;

Key Features

Unlike some databases that support materialized views (which store the results), DuckDB views are always virtual and compute their results on demand. This means they always return current data but may have performance implications for complex view definitions. Views can be particularly useful when working with DuckDB's support for directly querying files like CSV and Parquet, as they can provide a cleaner interface over file-based tables.

Common Use Cases

Views are commonly used to:

• Simplify complex joins or aggregations into a simple logical table
• Create standardized filters or transformations that can be reused
• Hide complexity of underlying data structures
• Provide row or column level security by exposing only certain data through views
• Create backward compatibility when schema changes occur