🪟Views

Named queries, not copied rows

A view stores a SELECT definition, clients see a stable virtual table while the engine usually expands the view into the outer query. Use views to simplify reporting, wrap row-level security predicates, or decouple application SQL from physical schema refactors.

SQL

Create view

CREATE VIEW reporting.v_open_orders AS
SELECT o.order_id, c.company_name, o.total_usd
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'OPEN';

✓Illustrative result set

result
CREATE VIEW

SQL

Query through the view

SELECT order_id, total_usd
FROM reporting.v_open_orders
WHERE company_name LIKE 'Helix%';

✓Illustrative result set

order_id	total_usd
501	120.5

🔐Security and mutability

Invoker vs definer and updatable paths vary by engine.

SECURITY INVOKER vs DEFINER

Definer rights can centralize privileges but widen blast radius if predicates are wrong, treat definer views like audited APIs with explicit column lists and tests.

Updatable views

Simple single-table projections may accept INSERT/UPDATE; complex joins often do not, or need INSTEAD OF triggers (SQL Server). Document read-only contracts for app code.

Opaque view stacks

SELECT * in views that ship to apps, column sets and defaults can shift underfoot.
Deep nested views that nobody can test or explain in isolation.

Explicit columns and plans

Project explicit columns on views that are part of your public surface.
Run EXPLAIN on representative queries through stacked views after schema changes.
Prefer CTEs for one-off analysis; reserve views for reused, versioned contracts.

Optimizer note

Many engines inline simple views; heavy nesting can confuse cardinality estimates, if latency regresses after a refactor, compare plans with the view expanded vs inlined.

TL;DR

A view is a stored SELECT with a name, no copied data unless materialized. It simplifies permissions (grant access to the view, not base tables) and encapsulates joins. Simple views are inlined like macros; complex views still execute underlying tables with the caller's privileges, security invoker vs definer matters for row-level access control.