CREATE VIEW

Creates or replaces a named view backed by a SQL query.

Category: ddl

Syntax

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] <name> AS SELECT ...

Description

## Overview Creates a named view that encapsulates a SQL query. Views are virtual tables that do not store data; the underlying query is executed each time the view is referenced in a SQL statement. ## Behavior - The view is first persisted to the catalog (if 3-part qualified), then registered with the DataFusion session. - CREATE OR REPLACE updates an existing view definition without requiring a DROP first. Both the catalog and session entries are updated. - IF NOT EXISTS prevents an error when the view already exists. If both OR REPLACE and IF NOT EXISTS are specified, OR REPLACE takes precedence. - The query is not materialized. Each reference to the view triggers execution of the underlying SELECT statement. - Views can reference other views, tables, and any SQL expressions supported by the query engine. ## Compatibility Views are registered with DataFusion and persisted to the catalog. They are accessible within the current session and, for 3-part names, across sessions via the catalog.

Parameters

Name	Type	Description
`view`		Fully qualified view name.
`or_replace`		Replace existing view.
`if_not_exists`		Don't error if view exists.
`as_query`		The SELECT query.

Examples

-- Create a simple view
CREATE VIEW warehouse.sales.active_customers AS
SELECT customer_id, name, email
FROM warehouse.sales.customers
WHERE status = 'active';

-- Create or replace an existing view
CREATE OR REPLACE VIEW warehouse.sales.monthly_revenue AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total) AS revenue
FROM warehouse.sales.orders
GROUP BY 1;

-- Create a view only if it does not exist
CREATE VIEW IF NOT EXISTS warehouse.sales.top_products AS
SELECT product_id, name, SUM(quantity) AS total_sold
FROM warehouse.sales.order_items
GROUP BY product_id, name
ORDER BY total_sold DESC
LIMIT 100;

Pitfalls

Views are not materialized. Complex queries in view definitions execute every time the view is used, which can impact performance for expensive aggregations or joins.
If underlying tables referenced by the view are dropped or renamed, the view becomes broken. There is no automatic dependency tracking.
CREATE VIEW IF NOT EXISTS does not update the view definition if it already exists. To update an existing view, use CREATE OR REPLACE VIEW.