Table of Contents
- Introduction
- What is a View in SQL?
- Benefits of Using Views
- Syntax for Creating a View
- Querying Data from a View
- Updating a View
- Rules and Limitations of Updatable Views
- Modifying an Existing View
- Dropping a View
- Using
WITH CHECK OPTION
- Views vs Tables
- Views vs CTEs
- Security and Access Control via Views
- Performance Considerations
- Materialized Views (vs Regular Views)
- Real-World Example: Simplifying Joins
- Real-World Example: Department-Level Reporting
- Best Practices for Working with Views
- Common Errors and Troubleshooting
- Summary and What’s Next
1. Introduction
SQL views are virtual tables built from queries. They allow you to simplify complex logic, encapsulate business rules, and present data in a structured way — without duplicating actual data.
2. What is a View in SQL?
A view is a saved SQL query that acts like a virtual table. When you select from a view, it runs the underlying query and returns the result set as if you queried a real table.
3. Benefits of Using Views
- Simplify complex queries
- Enforce business logic
- Control user access
- Reuse logic across applications
- Improve readability and maintainability
4. Syntax for Creating a View
sqlCopyEditCREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
Example:
sqlCopyEditCREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';
5. Querying Data from a View
sqlCopyEditSELECT * FROM active_customers;
You can use a view just like a table in SELECT
, JOIN
, WHERE
, etc.
6. Updating a View
sqlCopyEditCREATE OR REPLACE VIEW active_customers AS
SELECT id, name
FROM customers
WHERE is_active = 1;
Most SQL dialects support CREATE OR REPLACE
to update a view’s definition.
7. Rules and Limitations of Updatable Views
Some views allow updates (INSERT
, UPDATE
, DELETE
) if:
- Based on a single table
- No
GROUP BY
,DISTINCT
, or aggregate functions - No subqueries or joins
If these rules are violated, the view becomes read-only.
8. Modifying an Existing View
In MySQL and PostgreSQL:
sqlCopyEditCREATE OR REPLACE VIEW view_name AS
-- new query here
In SQL Server:
sqlCopyEditALTER VIEW view_name AS
-- new query here
9. Dropping a View
sqlCopyEditDROP VIEW view_name;
This removes the virtual table from the schema (not the underlying data).
10. Using WITH CHECK OPTION
Prevents updates through a view that would violate its conditions:
sqlCopyEditCREATE VIEW high_salary_employees AS
SELECT * FROM employees
WHERE salary > 100000
WITH CHECK OPTION;
Now, you cannot insert or update a row through this view unless it satisfies salary > 100000
.
11. Views vs Tables
Feature | Table | View (Virtual Table) |
---|---|---|
Stores data | Yes | No (except materialized views) |
Modifiable | Yes | Sometimes (updatable views) |
Physical storage | On disk | Not stored; computed on access |
Refresh needed | No | No (unless materialized) |
12. Views vs CTEs
Feature | View | CTE (WITH ) |
---|---|---|
Persistent | Yes | No (temporary for one query) |
Reusable | Across queries | Only in the current query |
Performance | May benefit from indexing | Typically not cached |
Ideal for | Reusable logic | Temporary intermediate logic |
13. Security and Access Control via Views
Views can limit access to sensitive data:
sqlCopyEditCREATE VIEW employee_public AS
SELECT name, department
FROM employees;
You can grant access to this view without exposing the full table:
sqlCopyEditGRANT SELECT ON employee_public TO analyst_role;
14. Performance Considerations
- Views don’t store data — each access reruns the query
- Complex views with joins and filters may slow down queries
- You can index base tables, but not views
- Consider materialized views for performance-sensitive cases
15. Materialized Views (vs Regular Views)
Materialized views store data physically:
Feature | Regular View | Materialized View |
---|---|---|
Data stored | No | Yes |
Always fresh | Yes (computed live) | No (requires manual/auto refresh) |
Performance | Slower | Faster for large static datasets |
16. Real-World Example: Simplifying Joins
sqlCopyEditCREATE VIEW order_summary AS
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id;
Now query reports using:
sqlCopyEditSELECT * FROM order_summary WHERE total > 500;
17. Real-World Example: Department-Level Reporting
sqlCopyEditCREATE VIEW department_sales AS
SELECT d.name AS department, SUM(o.total) AS total_sales
FROM orders o
JOIN employees e ON o.employee_id = e.id
JOIN departments d ON e.department_id = d.id
GROUP BY d.name;
Use for dashboards or monthly summaries.
18. Best Practices for Working with Views
- Name views meaningfully (
active_users
,monthly_sales
, etc.) - Use
WITH CHECK OPTION
if enforcing integrity - Avoid overly complex views with multiple nested joins
- Document views as part of schema design
- Don’t use views as a substitute for indexing or denormalization
19. Common Errors and Troubleshooting
Issue | Solution |
---|---|
View not updating | Check if base table changed or not |
Cannot modify data in view | Ensure it’s not based on join/grouped data |
Performance is slow | Simplify view query or use materialized view |
Invalid column in query | Update view definition using CREATE OR REPLACE |
20. Summary and What’s Next
Views in SQL act as virtual tables, helping you simplify complex logic, protect sensitive data, and write cleaner, reusable queries. Mastering views is essential for maintaining scalable and secure SQL systems.