Introduction
Complex queries can be simplified and reused through virtual tables called views.
CREATE VIEW statement creates stored query definitions that act like tables.
It is widely used for:
- Simplifying complex queries
- Data security (column restriction)
- Reusable query definitions
What is CREATE VIEW
View is a virtual table based on SELECT query results.
Doesn't store data, shows live results from base tables.
Basic Syntax
Example
Now query: SELECT * FROM ActiveStudents;
CREATE VIEW with Column List
Explicit column names.
CREATE OR REPLACE VIEW
Update existing view.
VIEW with JOIN
Complex multi-table views.
VIEW with Aggregate Functions
Pre-calculated summaries.
VIEW with WHERE Clause
Filtered datasets.
VIEW with ORDER BY
Pre-sorted results.
VIEW with Subqueries
Complex logic encapsulation.
CREATE VIEW AboveAverage AS
Updatable Views
Simple views can be updated.
-- Can UPDATE
Non-Updatable Views
Complex views cannot be updated.
-- UPDATE fails
Execution Order with Views
View query executes on every SELECT from view.
SELECT view → Execute view definition → Return results
Performance Considerations
- Views don't materialize (re-execute each time)
- Complex views slower than base tables
- Use materialized views for heavy queries
- Indexes on base tables still work
Important Notes
- Views don't store data (virtual tables)
- Simple views (no GROUP BY/JOIN) updatable
- CREATE OR REPLACE updates existing views
- Views can reference other views
- Permissions inherited from base tables
Example Scenario
Complete reporting view:
Common Mistakes
- Updating non-updatable views
- Expecting views to store data
- Complex view performance surprises
- Circular view references
- Missing base table permissions
Key Points to Remember
- CREATE VIEW stores query definition only
- Views execute fresh each SELECT
- Simple views updatable, complex not
- CREATE OR REPLACE for updates
- Perfect for simplifying complex queries
- Base table indexes still work