I’ve watched otherwise well-designed systems get bogged down by one familiar problem: the same “slightly different” reporting query copied into dashboards, cron jobs, API handlers, and ad-hoc scripts. Six months later, no one remembers which version is correct, a sensitive column slips into a CSV export, and every change becomes a risky search-and-replace across the codebase.
When that happens, I reach for a view. A view is a named query that behaves like a table. You query it the same way you query a real table, but the database builds the result on demand from the underlying tables. That single idea—“name the query and treat it like data”—is surprisingly powerful. It’s also easy to misuse if you treat views as magic performance boosters or as a replacement for proper modeling.
Here’s what you’ll get from me: how CREATE VIEW works, how to write view definitions you’ll be happy to maintain, how to use views for access control and stable “data contracts,” where updatable views help (and where they don’t), and the performance realities across common databases.
What a view really is (and isn’t)
A view is a virtual table defined by a SELECT statement. The view typically stores the definition (the SQL text / parsed plan metadata), not the rows. When you run:
SELECT * FROM some_view;…the database expands the view into its underlying query (conceptually), plans the full query, and returns rows from base tables.
A few practical implications I keep in my head:
- A view is not a cache. If you query it 10 times, you generally compute results 10 times (with whatever caching your database engine does internally, but you shouldn’t count on that for application design).
- A view is not a copy. If base table data changes, the view shows the change immediately the next time you query it.
- A view can hide complexity. That’s good when it creates a stable interface, and bad when it turns into a “SQL junk drawer” nobody understands.
- Permissions can be layered. In many databases, you can grant access to a view while restricting access to underlying tables or columns.
If you need precomputed results, look at materialized views (where supported) or an ETL/ELT workflow. I still like regular views as the default because they keep the “source of truth” in one place and remove duplication.
CREATE VIEW syntax that you’ll actually use
The core syntax is simple:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
In practice, real view definitions often include joins, computed columns, and filters. I also recommend you write view SQL like you’d write production code: consistent formatting, explicit column lists, and stable naming.
Here’s a complete, runnable example you can paste into a fresh database (works with minor tweaks across PostgreSQL, MySQL, SQL Server, and SQLite).
-- Base table
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price_usd DECIMAL(10, 2) NOT NULL
);
INSERT INTO products (productid, productname, price_usd) VALUES
(1, ‘USB-C Cable (2m)‘, 14.99),
(2, ‘Mechanical Keyboard‘, 129.00),
(3, ‘27-inch Monitor‘, 249.50),
(4, ‘Laptop Stand‘, 59.00);
-- View: “expensive products”
CREATE VIEW expensive_products AS
SELECT
product_id,
product_name,
price_usd
FROM products
WHERE price_usd > 100.00;
-- Query it like a table
SELECT *
FROM expensive_products
ORDER BY price_usd DESC;
I like this pattern because it encodes a business rule (“expensive means > 100 USD”) once, in one place. Your reporting queries, API code, or BI tool can all depend on the same definition.
A detail I want you to notice: I selected explicit columns. SELECT * inside a view is a future maintenance trap because adding a new column to the base table can silently change the view’s shape, which can break consumers in ways that are painful to debug.
Views as a stable interface (and a security boundary)
In my experience, the most valuable reason to create a view is not convenience—it’s stability.
If you treat a view as a contract, you can change underlying tables without breaking downstream consumers, as long as the view keeps the same columns and meanings. That’s a huge deal for teams running analytics dashboards, export jobs, partner reporting, or internal APIs.
Column-level exposure
Suppose your employees table contains a salary column that most readers should not see. A view can expose only the safe subset.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INTEGER NOT NULL,
salary_usd DECIMAL(12, 2) NOT NULL
);
CREATE VIEW employee_directory AS
SELECT
employee_id,
first_name,
last_name,
department_id
FROM employees;
Then you grant access to employee_directory rather than employees.
The exact permission commands vary:
- PostgreSQL:
GRANT SELECT ON employeedirectory TO appreadonly;and keepemployeeslocked down. - SQL Server:
GRANT SELECT ON dbo.employeedirectory TO appreadonly;and deny on base. - MySQL:
GRANT SELECT ON employeedirectory TO ‘appreadonly‘@‘%‘;.
Row-level exposure
A view can also filter rows. For example, your support team might only be allowed to see “active” customers.
CREATE VIEW active_customers AS
SELECT
customer_id,
email,
created_at
FROM customers
WHERE status = ‘active‘;
If you need strict guarantees against data “leaking” through tricky predicates, you should also look at your database’s row-level security features. I often combine them: row-level security for enforcement, views for ergonomics and a consistent interface.
Definer vs invoker behavior (why it matters)
Different databases evaluate view permissions differently:
- MySQL supports
SQL SECURITY DEFINERvsSQL SECURITY INVOKERfor views, which changes whether the view runs with the creator’s privileges or the caller’s. - PostgreSQL historically relied on privilege checks on underlying objects and offers view options aimed at safer patterns (and you can also use security-barrier behavior for some cases).
- SQL Server uses ownership chaining patterns that can allow access via views when ownership is consistent.
My rule: never treat a view as “security” unless you’ve verified the privilege model in your specific database and tested it with real roles. I’ve seen teams accidentally expose data because they assumed all engines behave the same.
Joined views for readable reporting (without SQL spaghetti)
Joined views are where CREATE VIEW starts paying rent fast.
Let’s build the classic “employees with department names” view. This example is intentionally small so you can run it end-to-end.
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(80) NOT NULL
);
INSERT INTO departments (departmentid, departmentname) VALUES
(10, ‘Engineering‘),
(20, ‘Sales‘),
(30, ‘Support‘);
-- Reuse employees table from earlier, or create a minimal one:
-- (If you already created employees above, skip the CREATE TABLE.)
INSERT INTO employees (employeeid, firstname, lastname, departmentid, salary_usd) VALUES
(1, ‘Ava‘, ‘Patel‘, 10, 165000.00),
(2, ‘Noah‘, ‘Kim‘, 20, 120000.00),
(3, ‘Mia‘, ‘Lopez‘, 30, 98000.00);
CREATE VIEW employeedepartmentinfo AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM employees e
JOIN departments d
ON d.departmentid = e.departmentid;
SELECT *
FROM employeedepartmentinfo
ORDER BY departmentname, lastname;
This view becomes a convenient building block:
- Your BI tool can query
employeedepartmentinfowithout needing to understand join keys. - Your API can safely expose the joined shape without embedding join logic in application code.
- You can extend the view later (carefully) by adding computed columns like
full_name.
One analogy I use when teaching this: a view is like a saved “lens” on your database. You can still reach for raw tables when you need to, but most day-to-day work is easier through well-chosen lenses.
Aggregation views: the “metrics layer” you control
Where joined views simplify shape, aggregated views simplify meaning.
Consider order reporting. You might have an orders table and an order_items table, then repeat the same revenue math everywhere. I prefer to encode that once.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
ordered_at TIMESTAMP NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
sku VARCHAR(40) NOT NULL,
quantity INTEGER NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders (orderid, customerid, ordered_at) VALUES
(1001, 501, ‘2026-01-02 10:00:00‘),
(1002, 502, ‘2026-01-03 12:30:00‘);
INSERT INTO orderitems (orderid, sku, quantity, unit_price) VALUES
(1001, ‘KB-104‘, 1, 129.00),
(1001, ‘CB-USBC-2M‘, 2, 14.99),
(1002, ‘MON-27‘, 1, 249.50);
CREATE VIEW order_totals AS
SELECT
o.order_id,
o.customer_id,
o.ordered_at,
SUM(oi.quantity * oi.unitprice) AS ordertotal_usd,
SUM(oi.quantity) AS items_count
FROM orders o
JOIN order_items oi
ON oi.orderid = o.orderid
GROUP BY
o.order_id,
o.customer_id,
o.ordered_at;
SELECT *
FROM order_totals
ORDER BY ordered_at;
When you centralize this calculation:
- You reduce inconsistent definitions (“gross revenue” vs “net revenue” vs “subtotal”).
- You make changes safer. If you later add discounts, taxes, or refunds, you change the view and re-run reports.
- You get a cleaner boundary between “raw events” and “business meaning.”
A warning I always share: aggregated views can hide join fanout bugs. If you accidentally join another table that duplicates rows (for example, multiple addresses per customer), your SUM(...) can inflate. When a number looks wrong, I first validate join cardinality and add sanity checks like COUNT(DISTINCT ...) in exploratory queries.
Updatable views, CHECK OPTION, and what breaks
People often ask: “Can I INSERT or UPDATE a view?” Sometimes yes.
If a view is simple enough—often a single-table view without aggregation or complex joins—many databases allow updates through it. This can be a nice way to enforce a constrained editing surface.
Here’s a realistic pattern: a view that exposes only active products, and you want to prevent accidental writes that would move a row outside the view.
-- View over a single table with a filter
CREATE VIEW active_products AS
SELECT
product_id,
product_name,
price_usd
FROM products
WHERE price_usd > 0;
-- Some databases support CHECK OPTION (syntax varies slightly)
-- Standard-ish form:
CREATE VIEW activeproductsstrict AS
SELECT
product_id,
product_name,
price_usd
FROM products
WHERE price_usd > 0
WITH CHECK OPTION;
With a check option, an update that would make price_usd become 0 should fail because the resulting row wouldn’t satisfy the view predicate.
What breaks updatability:
GROUP BY, aggregates,DISTINCTUNION/ set operations- Many joins (some engines support certain join updates, but it’s complex)
- Computed columns that don’t map cleanly back to base columns
My practical guidance:
- If you want views primarily for reads (reporting, analytics, permissions), keep them read-focused and don’t rely on updating through them.
- If you want a constrained write surface, prefer explicit stored procedures or carefully designed updatable views that are small and heavily tested.
And yes: I test view updatability with actual roles. It’s easy to assume a view is updatable because it “looks simple,” then discover your engine rejects updates due to a subtle rule.
Performance realities: when views help and when they hurt
A view can make your SQL easier to read, but it does not guarantee faster queries.
Here’s the mental model I use:
- The database generally “inlines” a view into the outer query.
- Indexes still matter on base tables, not on the view.
- The query planner can still push predicates down into the base tables in many cases.
So why do people see performance changes after adding views?
When views help performance indirectly
- You standardize the “right” join path. Fewer accidental cross joins, fewer missing predicates.
- You reduce accidental overfetching by exposing fewer columns (less I/O in some workloads).
- You nudge teams toward consistent filtering (for example, excluding soft-deleted rows).
In real systems, those effects can shave a query from “hundreds of ms” down to “tens of ms” simply by preventing mistakes, not by changing what the database can do.
When views hurt
- Stacking views on views can create deeply nested logic that’s hard to reason about.
- Some engines struggle to rewrite complex view stacks, leading to poor plans.
- A view that hides expensive operations (like wide joins or window functions) can encourage careless use.
When I suspect a view is causing trouble, I do three things:
1) Inspect the execution plan for a representative query.
2) Query the base tables with the same predicates to confirm where time is spent.
3) Consider turning the expensive part into a materialized view or a precomputed table in the analytics pipeline.
Materialized views (and why I treat them differently)
A materialized view stores results physically and refreshes them. It’s great for expensive aggregates and dashboards that can tolerate slightly stale data. If you need “fresh within seconds,” you might refresh frequently; if you need “fresh daily,” refresh nightly.
The trade you are making is compute-on-read vs compute-on-refresh. I often start with a normal view, measure, then graduate to a materialized view only when the cost justifies the extra operational work.
Operational practices I follow in 2026 (migrations, CI, and AI-assisted review)
Views are part of your schema. Treat them the same way you treat tables: versioned, reviewed, tested.
Put view definitions under source control
I store view definitions in migration files or schema management tooling. The exact tool varies by team, but common choices include Flyway, Liquibase, Sqitch, Atlas, and framework-native migrations.
A simple migration-friendly approach:
- One file per view definition (
views/employeedepartmentinfo.sql) - A repeatable migration that re-creates the view
- A CI check that applies migrations to a clean database
Choose a strategy for changes
I use one of these patterns:
CREATE OR REPLACE VIEWwhere supported (fast iteration, stable object name)DROP VIEW ...thenCREATE VIEW ...inside a transaction (more portable, but mind dependencies)
If other views depend on a view you’re changing, dropping it can cascade failures. I keep dependencies visible and prefer “replace” semantics when the engine supports it.
Add automated checks
Even without heavy test harnesses, you can add lightweight guardrails:
- A CI job that runs
SELECTagainst each view to ensure it compiles. - A lint rule: forbid
SELECT *in view definitions. - A smoke query that checks key invariants (for example,
ordertotalusdis never negative).
Traditional vs modern workflow (what I recommend)
Here’s the shift I’ve seen work well:
What it looks like
What I recommend instead
—
—
Views created manually in prod, copied into wikis
Migrations + repeatable view scripts
Business logic scattered in app SQL strings
Views as stable contracts
Semantic layer defined in code, validated in CI
Start small: a few high-value views
AI-assisted PR review for SQL diffs
Use AI for checklisting and risk spotting, not as the final authority### AI-assisted review (useful when scoped)
I’m happy to use AI tooling for two kinds of work:
- Spotting risk: “Does this change expose any PII columns?”, “Does this join multiply rows?”, “Is the predicate consistent with soft-delete rules?”
- Generating tests: “Write three assertions that validate the view returns expected columns and doesn’t duplicate order IDs.”
I keep it bounded. The database is still the source of truth, and the execution plan still decides what happens at runtime.
Common mistakes I see (and how I avoid them)
1) Using SELECT * in a view definition
– Why it hurts: it makes the view’s contract unstable.
– What I do: list columns explicitly, always.
2) Treating views as a performance feature
– Why it hurts: teams stop measuring and start assuming.
– What I do: start with a view for clarity, then measure. If you need stored results, use a materialized view or precompute.
3) Building “view stacks” too early
– Why it hurts: nested views make debugging plans and correctness harder.
– What I do: keep layers shallow. If I need multiple layers, I name them by intent (raw, clean, reporting_) and document ownership.
4) Using views as the only security control
– Why it hurts: privilege behavior differs across engines and configurations.
– What I do: test roles explicitly, and pair views with native security features where needed.
5) Forgetting dependency management
– Why it hurts: dropping/replacing a view can break other views, stored procedures, or reports.
– What I do: keep schema changes in migrations and run them on a clean database in CI.
6) Hiding business definitions inside ad-hoc queries
– Why it hurts: “revenue,” “active,” and “churned” end up defined five ways.
– What I do: define key metrics in one or two canonical views, then reference them everywhere.
If you take one action after reading this, make it this: pick one repeated query in your system—something used by a dashboard, a weekly report, or a customer export—and turn it into a view with an explicit column list and a name that reflects business meaning. Then grant access to the view instead of the underlying table if the data is sensitive. That single change reduces duplication, makes future schema work safer, and gives you a clear place to review what data you’re exposing.
Next, run one performance sanity check: compare the execution plan for a common view query versus the equivalent base-table query with the same filters. If the plan looks reasonable, you’re done. If it’s expensive, you now have a clean target for improvement—add the right indexes to base tables, reduce join fanout, or move heavy aggregation into a materialized view or a scheduled pipeline.
Finally, treat views like code. Put definitions in version control, run a CI migration test, and add a couple of invariants as lightweight queries. The payoff isn’t theoretical; it’s fewer late-night surprises when someone asks, “Why does this report disagree with that report?” and you can point to one canonical definition instead of five drifting copies.
