SQL Views

A View is a virtual table based on a SQL query result set. It doesn't store data physically (except materialized views) but provides abstraction, security, and query simplification.

View Architecture

flowchart TB
    A[User Query] --> B[View Definition]
    B --> C[Query Expansion]
    C --> D[Query Optimizer]
    D --> E[Base Tables]
    E --> F[Execute Query]
    F --> G[Return Results]
    
    H[View Metadata] --> B
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style E fill:#FF9800
    style G fill:#9C27B0

Key Points:

• Virtual Table: View stores query definition, not data
• Query Expansion: View definition merged with user query
• Optimization: Database optimizes combined query
• Base Tables: Actual data retrieved from underlying tables
• No Storage: Regular views don't consume storage space (metadata only)

View Types Comparison

graph TB
    A[SQL Views] --> B[Simple Views]
    A --> C[Complex Views]
    A --> D[Materialized Views]
    A --> E[Indexed Views]
    
    B --> B1[Single Table]
    B --> B2[No Aggregates]
    B --> B3[Updatable]
    
    C --> C1[Multiple Tables]
    C --> C2[Joins/Aggregates]
    C --> C3[Read-Only]
    
    D --> D1[Physical Storage]
    D --> D2[Periodic Refresh]
    D --> D3[Fast Queries]
    
    E --> E1[Materialized + Indexed]
    E --> E2[SQL Server]
    E --> E3[Best Performance]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336

Key Points:

• Simple Views: Single table, no aggregates, usually updatable
• Complex Views: Multiple tables with joins, aggregates, read-only
• Materialized Views: Store results physically, require refresh
• Indexed Views: Materialized with indexes for maximum performance

Regular vs Materialized Views

sequenceDiagram
    participant User
    participant RegView as Regular View
    participant MatView as Materialized View
    participant Tables as Base Tables
    participant Cache as Cached Data
    
    User->>RegView: SELECT * FROM view
    RegView->>Tables: Execute query
    Tables->>RegView: Return data
    RegView->>User: Results
    
    User->>MatView: SELECT * FROM mat_view
    MatView->>Cache: Read cached data
    Cache->>MatView: Return data
    MatView->>User: Results (Fast!)
    
    Note over MatView,Cache: Refresh periodically

Key Points:

• Regular View: Executes query every time, always current data
• Materialized View: Reads from cache, much faster but may be stale
• Refresh Strategy: Complete, fast, on-commit, or on-demand
• Trade-off: Speed vs data freshness

View Query Execution Flow

flowchart LR
    A[SELECT * FROM view<br/>WHERE city = NYC] --> B[View Definition<br/>Expansion]
    B --> C[Merge Predicates]
    C --> D[Apply Indexes]
    D --> E[Execute on<br/>Base Tables]
    E --> F[Return Results]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style E fill:#9C27B0

Key Points:

• Query Merging: User query combined with view definition
• Predicate Pushdown: WHERE clauses applied to base tables
• Index Usage: Optimizer uses indexes on base tables
• Transparent: Users query views like regular tables

Updatable View Conditions

graph TB
    A[Updatable View?] --> B{Single Table?}
    B -->|No| C[Not Updatable]
    B -->|Yes| D{Has DISTINCT?}
    D -->|Yes| C
    D -->|No| E{Has GROUP BY?}
    E -->|Yes| C
    E -->|No| F{Has Aggregates?}
    F -->|Yes| C
    F -->|No| G[Updatable!]
    
    style A fill:#2196F3
    style G fill:#4CAF50
    style C fill:#F44336

Key Points:

• Single Table: Must reference only one base table
• No Aggregates: Cannot use SUM, COUNT, AVG, etc.
• No DISTINCT: Cannot use DISTINCT keyword
• No GROUP BY: Cannot group results
• Simple Columns: Direct column references only

Security with Views

flowchart TB
    A[Users/Roles] --> B[View Layer]
    
    B --> C[Row-Level Security]
    B --> D[Column-Level Security]
    
    C --> E[WHERE Filters]
    D --> F[SELECT Specific Columns]
    
    E --> G[Base Tables]
    F --> G
    
    G --> H[Full Data<br/>Sensitive Info]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style G fill:#F44336

Key Points:

• Row Filtering: WHERE clause restricts visible rows
• Column Filtering: SELECT hides sensitive columns
• Access Control: Grant permissions on views, not base tables
• Data Masking: Transform sensitive data in view definition

WITH CHECK OPTION

sequenceDiagram
    participant User
    participant View
    participant CheckOption as WITH CHECK OPTION
    participant Table
    
    User->>View: UPDATE active_customers<br/>SET active = 0
    View->>CheckOption: Validate update
    CheckOption->>CheckOption: Check WHERE active = 1
    CheckOption->>View: Violation!
    View->>User: Error: Check constraint
    
    Note over CheckOption: Prevents updates that<br/>violate view condition

Key Points:

• Constraint Enforcement: Ensures updates satisfy view's WHERE clause
• Prevents Disappearing Rows: Rows can't be updated out of view
• LOCAL vs CASCADED: LOCAL checks current view, CASCADED checks all
• Best Practice: Always use for updatable views with WHERE clause

Materialized View Refresh

flowchart TB
    A[Refresh Strategy] --> B[Complete Refresh]
    A --> C[Fast Refresh]
    A --> D[On Commit]
    A --> E[On Demand]
    
    B --> B1[Rebuild Entire View]
    B --> B2[Slow but Accurate]
    
    C --> C1[Incremental Updates]
    C --> C2[Requires MV Logs]
    
    D --> D1[After Each Transaction]
    D --> D2[Always Current]
    
    E --> E1[Manual/Scheduled]
    E --> E2[Flexible Timing]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336

Key Points:

• Complete: Rebuilds entire view, slow but guaranteed accurate
• Fast: Updates only changed data, requires materialized view logs
• On Commit: Refreshes after transactions, keeps data current
• On Demand: Manual or scheduled refresh, balances freshness and performance

Code Examples

Simple View

-- Create simple view
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE active = 1;

-- Query view
SELECT * FROM active_customers WHERE last_name LIKE 'S%';

Complex View with Joins

-- Multi-table view
CREATE VIEW order_summary AS
SELECT 
    o.order_id,
    c.first_name,
    c.last_name,
    o.order_date,
    o.total_amount,
    COUNT(oi.item_id) as item_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, c.first_name, c.last_name, 
         o.order_date, o.total_amount;

Materialized View (PostgreSQL)

-- Create materialized view
CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    product_id,
    COUNT(*) as total_sales,
    SUM(quantity) as total_quantity,
    SUM(total_amount) as revenue
FROM order_items
GROUP BY product_id;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW sales_summary;

-- Concurrent refresh (non-blocking)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Updatable View with CHECK OPTION

-- Prevent updates that violate WHERE clause
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, active
FROM customers
WHERE active = 1
WITH CHECK OPTION;

-- This will fail (violates WHERE active = 1)
UPDATE active_customers SET active = 0 WHERE customer_id = 1;

Security Views

-- Hide sensitive columns
CREATE VIEW employee_public AS
SELECT employee_id, first_name, last_name, email, department
FROM employees;
-- Excludes: salary, ssn, bank_account

-- Department-specific view
CREATE VIEW dept_employees AS
SELECT * FROM employees WHERE department_id = 10;

GRANT SELECT ON employee_public TO public_users;
GRANT SELECT ON dept_employees TO dept_manager;

Best Practices

1. Naming: Use prefixes (v_, vw_, mv_) to identify views
2. **Avoid SELECT ***: Specify columns explicitly for stability
3. Index Base Tables: Views use base table indexes
4. Limit Nesting: Avoid deep view hierarchies (max 2 levels)
5. Document: Comment view purpose and dependencies
6. Use Materialized: For complex, slow queries with acceptable staleness

---