You've written the same 12-line JOIN query for the third time this week. It lives in your reporting script, your admin dashboard, and your data export job. Every time the business logic changes, you update it in three places — and inevitably miss one.
SQL views were built to fix exactly this. A view is a named, stored query that you can treat like a table. Define your logic once, then reference it from anywhere. This article walks through everything you need to know to start using views effectively: what they are, how to create them, real-world scenarios where they shine, and the gotchas that trip people up.
What Is a SQL View?
A view is a virtual table based on the result of a SELECT statement. It doesn't store data itself — it stores the query. Every time you SELECT from a view, the database runs that underlying query and returns fresh results.
Here's the simplest possible view:
CREATE VIEW active_customers AS
SELECT id, name, email, created_at
FROM customers
WHERE status = 'active';
Now instead of repeating that filter everywhere, you just do:
SELECT * FROM active_customers;
The database sees through the view and runs the full underlying query — you get current data, every time.
Creating, Replacing, and Dropping Views
CREATE VIEW
CREATE VIEW monthly_revenue AS
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;
Once this exists, any analyst on your team can run:
SELECT * FROM monthly_revenue WHERE month >= '2026-01-01';
No need to remember the aggregation logic — it's baked into the view.
CREATE OR REPLACE VIEW
To update a view's definition without dropping and recreating it:
CREATE OR REPLACE VIEW monthly_revenue AS
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count,
AVG(total_amount) AS avg_order_value -- new column added
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;
DROP VIEW
DROP VIEW IF EXISTS monthly_revenue;
Use IF EXISTS to avoid errors if the view doesn't exist yet.
Real-World Use Cases
1. Simplifying Complex Reports
Imagine you have an e-commerce schema: orders, order_items, products, customers. A typical report query might join all four tables and apply several filters. Put that in a view:
CREATE VIEW order_summary AS
SELECT
o.id AS order_id,
c.name AS customer_name,
c.email,
o.order_date,
o.status,
SUM(oi.quantity * oi.unit_price) AS total_amount,
COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, c.email, o.order_date, o.status;
Now your dashboard queries look clean:
-- Top 10 customers by spending this year
SELECT customer_name, email, SUM(total_amount) AS lifetime_value
FROM order_summary
WHERE order_date >= '2026-01-01'
GROUP BY customer_name, email
ORDER BY lifetime_value DESC
LIMIT 10;
2. Hiding Sensitive Columns
You want your analytics team to query the employees table but not see salaries or social security numbers. Create a view that exposes only safe columns:
CREATE VIEW employees_public AS
SELECT
id,
first_name,
last_name,
department,
job_title,
hire_date
FROM employees;
-- salary, ssn, and bank_account columns are never exposed
Grant access to the view, not the base table:
GRANT SELECT ON employees_public TO analytics_role;
This is one of the most practical uses of views in production systems.
3. Maintaining Backward Compatibility
Your application queries a table called user_profiles. You need to refactor the schema — split it into users and profile_details. Create a view with the old name that reconstructs the original shape:
CREATE VIEW user_profiles AS
SELECT
u.id,
u.email,
u.created_at,
pd.bio,
pd.avatar_url,
pd.location
FROM users u
JOIN profile_details pd ON pd.user_id = u.id;
Old queries keep working. You can migrate downstream consumers at your own pace.
4. Pre-Filtering for Multi-Tenant Apps
In a SaaS app, data is often segmented by tenant_id. Views can bake in row-level filtering:
CREATE VIEW tenant_orders AS
SELECT *
FROM orders
WHERE tenant_id = current_setting('app.current_tenant')::int;
(PostgreSQL supports this pattern using session-level settings.)
Updatable Views
In some situations you can run INSERT, UPDATE, and DELETE directly on a view. This works when the view is simple enough that the database can unambiguously map the change back to a single base table row.
A view is generally updatable if it:
- Selects from a single base table
- Does not use
DISTINCT,GROUP BY,HAVING,UNION, or aggregate functions - Does not include computed columns
Example of an updatable view:
CREATE VIEW active_products AS
SELECT id, name, price, stock_quantity
FROM products
WHERE is_active = TRUE;
-- This works because it maps cleanly to one row in `products`
UPDATE active_products SET price = 29.99 WHERE id = 42;
WITH CHECK OPTION
Add WITH CHECK OPTION to prevent updates that would push rows out of the view's filter:
CREATE VIEW active_products AS
SELECT id, name, price, stock_quantity
FROM products
WHERE is_active = TRUE
WITH CHECK OPTION;
-- This would fail — it would make the row invisible to the view
UPDATE active_products SET is_active = FALSE WHERE id = 42;
-- ERROR: new row violates check option for view "active_products"
Views vs. CTEs: When to Use Which
Both views and CTEs (covered in a previous article) let you name and reuse query logic. The key difference:
| Feature | View | CTE |
|---|---|---|
| Persistence | Stored in database permanently | Exists only during one query |
| Reusability | Available to all queries, all users | Local to the single SQL statement |
| Access control | Can be granted/revoked independently | Not applicable |
| Best for | Shared, frequently used logic | Ad-hoc, one-off query breakdowns |
| Recursive queries | No | Yes |
Rule of thumb: If you're writing the same subquery in multiple queries or multiple applications, make it a view. If you're just breaking up a complex single query for readability, use a CTE.
Common Mistakes and Gotchas
1. Assuming Views Cache Results
Views do not store data. Every query against a view re-executes the underlying SQL. If that underlying query is slow, your view will be slow too. Views give you reusability, not performance — for that, look at materialized views (a topic for another day).
2. Nesting Views Too Deeply
It's tempting to build views on top of views on top of views. Resist this. Deep view nesting makes debugging painful and can confuse the query planner. If your view references another view which references another view, the optimizer has to unpack all of them before building an execution plan. Keep hierarchies shallow (two levels max).
3. Putting Too Much Logic Into One View
Views encourage DRY code, but that doesn't mean one monster view should serve every use case. A view that joins 8 tables for every query will be slow for callers who only need 2 of those tables. Create targeted views for specific, well-understood use cases.
4. Forgetting That Schema Changes Can Break Views
If you drop a column from a base table that a view depends on, the view breaks — often silently until someone queries it. Most databases won't warn you at ALTER TABLE time. Build a habit of checking view dependencies before modifying base tables.
In PostgreSQL, you can check what views depend on a table:
SELECT viewname, definition
FROM pg_views
WHERE definition ILIKE '%your_table_name%';
5. Trying to Pass Parameters Into Views
Views are static — they can't accept parameters the way stored procedures can. If you find yourself creating dozens of near-identical views (orders_2024, orders_2025, orders_jan, orders_feb), that's a sign you want a parameterized query, a function, or a filtered query against a single general-purpose view instead.
Key Takeaways
SQL views are a powerful tool for keeping your database logic clean, consistent, and secure. In summary:
- A view is a named, stored SELECT query — not stored data
- Use them to centralize shared query logic so changes propagate everywhere at once
- Use them to restrict access by exposing only safe columns to certain roles
- Use
CREATE OR REPLACEto update view definitions without downtime - Use
WITH CHECK OPTIONto enforce constraints on updatable views - Prefer CTEs for one-off breakdowns, views for recurring shared logic
- Watch out for deep nesting, bloated view definitions, and silent schema-change failures
Try It Yourself
Take one query you write repeatedly — a filter, a join, a report — and convert it into a view today. Then ask: who else on your team could benefit from having access to it?
Have you run into situations where views saved you from repetition, or caused unexpected performance headaches? Drop a comment below — I'd love to hear how you're using them in production.
This is part of an ongoing series on SQL databases. Previous articles covered CTEs, window functions, indexes, and more. Follow along for new posts every few days.
Top comments (1)
You are right.