SQL Views

A view is a stored query with a name. Once customer_revenue exists as a view, every query can select from it as though it were a table, and nobody has to paste the same three-table join again.

A view holds no rows of its own. It holds the query text, which the database runs each time you reference the view. That single fact explains most of what follows: a view is always as fresh as the tables underneath it, and it is only as healthy as they are.

create view customer_revenue as
select o.customer_id,
       sum(oi.quantity * oi.unit_price) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
group by o.customer_id

select c.first_name,
       c.last_name,
       r.revenue
from customer_revenue r
join customers c on c.customer_id = r.customer_id
order by r.revenue desc, c.last_name

Replacing a view

CREATE OR REPLACE VIEW redefines an existing view in one statement, which is what makes a setup script safe to rerun.

create or replace view customer_revenue as
select o.customer_id,
       sum(oi.quantity * oi.unit_price) as revenue,
       count(distinct o.order_id) as order_count
from orders o
join order_items oi on oi.order_id = o.order_id
group by o.customer_id

Naming the columns

A view takes its column names from the query's output, so alias every expression you care about. You can also name the columns on the view itself, which is useful when the underlying query is generated or hard to edit.

create or replace view revenue_by_state (state, revenue) as
select c.state,
       sum(oi.quantity * oi.unit_price)
from customers c
join orders o on o.customer_id = c.customer_id
join order_items oi on oi.order_id = o.order_id
group by c.state

Listing more names than the query returns columns is an error in DuckDB (More VIEW aliases than columns in query result). Listing fewer is not: the names you give apply to the leading columns, and the rest keep whatever the query called them. A partial list is an easy way to end up with a view whose last columns are named sum(x).

Inspecting a view

A view's definition is stored in the catalog, so you can always read back what it actually does rather than trusting the name.

select view_name, sql
from duckdb_views()
where view_name = 'customer_revenue'

DESCRIBE customer_revenue gives the column names and types instead. The portable spelling is information_schema.views, whose view_definition column carries the same SQL and works on most engines.

Renaming and dropping

alter view revenue_by_state rename to state_revenue;

drop view if exists state_revenue;

Use DROP VIEW, not DROP TABLE. DuckDB checks the object type and refuses the mismatch with Existing object customer_revenue is of type View, trying to drop type Table, which is a good error to recognize when a cleanup script fails halfway through.

What breaks a view

A view records the names it was written against and resolves them when you query it, not when you create it. So nothing stops you from removing what it depends on. The view fails the next time somebody selects from it.

In DuckDB, dropping a table that a view is built on succeeds. The view stays in the catalog, and selecting from it then fails with Catalog Error: Table with name order_items does not exist!. Even DESCRIBE fails, because describing the view means resolving its query. Recreating the table with the same name and columns brings the view back to life.

Replacing a view has the same delayed effect on anything built on top of it. Drop a column from customer_revenue and a second view that selected that column keeps working right up until someone queries it, then reports Binder Error: Referenced column "revenue" not found in FROM clause!.

Views are read-only

You select from a view; you do not write to it. DuckDB rejects INSERT into a view with customer_revenue is not an table and UPDATE with Can only update base table. Change the underlying tables instead; the view reflects the change immediately, because it re-runs. Some other engines allow writes through simple single-table views, but none will write through an aggregating view like this one on its own. PostgreSQL, SQL Server, and Oracle can only do it with an INSTEAD OF trigger you write yourself.

View vs CTE vs table

All three give a query a name. They differ in how long the name lives and whether any rows are stored.

When you want stored rows instead

Because a view re-runs, an expensive view is expensive every single time, and its answer moves as the data moves. When you want the opposite, one computation held still, store the result in a table.

create or replace table customer_revenue_snapshot as
select * from customer_revenue

Insert a new order after that, and customer_revenue reflects it on the next query while customer_revenue_snapshot keeps the older number until you rebuild it. That is the whole trade: the view is always current, the snapshot is always reproducible.

Temporary views

CREATE TEMP VIEW makes a view that belongs to your connection and disappears with it. That is useful for shaping a scratch query without leaving an object behind for the next person.

create temp view active_customers as
select customer_id, first_name, last_name
from customers
where is_active;

select count(*) as active_count
from active_customers;

Practical habits

• Name views for what they return, not how they are built: customer_revenue, not orders_join_items_v2.
• Alias every computed column, so nobody inherits a column called sum((oi.quantity * oi.unit_price)).
• Put ORDER BY in the query that reads the view, not in the view. The reader controls the sort, and the view stays reusable.
• Write setup scripts with CREATE OR REPLACE VIEW and DROP VIEW IF EXISTS so rerunning them is safe.
• After dropping or reshaping a table, select from the views that depend on it. Nothing else will tell you they broke.