SQL Views
Here is a clear, practical, and complete guide to SQL Views, including syntax, behavior, updatable vs. non-updatable views, and examples for MySQL, PostgreSQL, SQL Server, and Oracle.
A VIEW is a virtual table created from a SQL query.
A view:
Does not store data (unless it’s a materialized view)
Is used like a table:
SELECT * FROM view_name;Helps simplify complex queries
Improves security by restricting sensitive columns
Provides abstraction and cleaner code
Basic Syntax — CREATE VIEW
Example: Simple View
Example: View with JOIN
Example: View for Aggregation
CREATE OR REPLACE VIEW (PostgreSQL, MySQL 8+, SQL Server)
Update a View (SQL Server)
Dropping a View
Safe drop:
Updatable Views (Important!)
Some views allow INSERT, UPDATE, and DELETE on them.
A view is updatable if:
Based on a single table
No GROUP BY
No DISTINCT
No aggregate functions
No subqueries
No UNION
Example (updatable):
Example (NOT updatable):
WITH CHECK OPTION (Ensures Data Integrity)
Prevents inserting or updating rows that don’t satisfy the view’s WHERE clause.
MATERIALIZED VIEWS (PostgreSQL, Oracle, SQL Server Indexed Views)
Materialized views store the query result physically.
Faster for large aggregations
Must be refreshed
PostgreSQL Example:
Advantages of Views
Security
Expose only required columns:
Simplify complex queries
Use a short SELECT instead of a long JOIN.
Reusable logic
Write once → use everywhere.
Reduce code duplication
Business logic centralized in one place.
Disadvantages of Views
- Can hide performance problems
- Harder to index (views themselves aren’t indexed)
- Complex nested views become unreadable
- Not all views are updatable
Best Practices
- Prefer
CREATE OR REPLACE VIEWfor safe updates - Use meaningful names (e.g.,
vw_active_users) - Avoid nested views unless necessary
- Use materialized views for expensive calculations
- Document whether a view is updatable or not
- Index underlying tables for performance
