SQL Views - Complete Guide with Diagrams
Master SQL views with visual diagrams, practical examples, and best practices. Learn simple views, complex views, materialized views, and indexed views.
SQL Views - Complete Guide
Table of Contents
- Introduction to Views
- Creating Views
- Querying Views
- Updatable Views
- Materialized Views
- Indexed Views
- View Management
- Security with Views
- Performance Considerations
- Best Practices
- Common Use Cases
- Interview Questions
Introduction to Views
A View is a virtual table based on the result set of a SQL query. It contains rows and columns just like a real table, but doesn't store data physically (except for materialized views).
What is a View?
A view is essentially a saved SQL query that you can treat as a table. When you query a view, the database executes the underlying query and returns the results.
View Architecture
┌─────────────────────────────────────────────────────────────┐
│ VIEW ARCHITECTURE │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┐ │
│ │ User Query │ │
│ │ SELECT * │ │
│ │ FROM view │ │
│ └──────┬───────┘ │
│ │ │
│ ▼ │
│ ┌──────────────┐ │
│ │ View │ ← Virtual table (no data storage) │
│ │ Definition │ │
│ └──────┬───────┘ │
│ │ │
│ ▼ │
│ ┌──────────────┐ │
│ │ Base Tables │ ← Actual data storage │
│ │ (Physical) │ │
│ └──────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Types of Views
┌──────────────────────────────────────────────────────────┐
│ TYPES OF VIEWS │
├──────────────────────────────────────────────────────────┤
│ │
│ 1. Simple Views │
│ • Based on single table │
│ • No aggregate functions │
│ • Usually updatable │
│ • Direct mapping to base table │
│ │
│ 2. Complex Views │
│ • Multiple tables (joins) │
│ • Aggregate functions │
│ • GROUP BY clauses │
│ • Usually read-only │
│ │
│ 3. Materialized Views │
│ • Physical storage of results │
│ • Periodic refresh │
│ • Better performance │
│ • Requires refresh strategy │
│ │
│ 4. Indexed Views │
│ • Materialized with indexes │
│ • SQL Server specific │
│ • Significant performance boost │
│ • Requires SCHEMABINDING │
│ │
└──────────────────────────────────────────────────────────┘
Key Benefits
- Security: Hide sensitive columns and rows
- Simplification: Abstract complex queries
- Consistency: Standardize data access patterns
- Abstraction: Shield applications from schema changes
- Reusability: Define once, use everywhere
- Performance: Pre-computed results with materialized views
Creating Views
Simple View Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example: Customer Contact View
-- Simple view for customer contacts
CREATE VIEW customer_contacts AS
SELECT
customer_id,
first_name,
last_name,
email,
phone
FROM customers
WHERE active = 1;
-- Query the view
SELECT * FROM customer_contacts;
-- Query with additional filter
SELECT * FROM customer_contacts
WHERE last_name LIKE 'S%';
Complex View with Joins
-- View combining multiple tables
CREATE VIEW order_summary AS
SELECT
o.order_id,
c.customer_id,
c.first_name,
c.last_name,
c.email,
o.order_date,
o.total_amount,
o.status,
COUNT(oi.item_id) as item_count
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY o.order_id, c.customer_id, c.first_name, c.last_name,
c.email, o.order_date, o.total_amount, o.status;
View with Aggregations
-- Aggregated view for sales analysis
CREATE VIEW monthly_sales AS
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value,
MIN(total_amount) AS min_order,
MAX(total_amount) AS max_order
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;
View Data Flow
┌─────────────────────────────────────────────────────────┐
│ VIEW QUERY EXECUTION FLOW │
├─────────────────────────────────────────────────────────┤
│ │
│ SELECT * FROM customer_contacts WHERE city = 'NYC' │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────┐ │
│ │ View Definition Expansion │ │
│ │ SELECT customer_id, first_name... │ │
│ │ FROM customers │ │
│ │ WHERE active = 1 AND city = 'NYC' │ │
│ └──────────────┬───────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────┐ │
│ │ Query Optimizer │ │
│ │ • Merge predicates │ │
│ │ • Apply indexes │ │
│ │ • Choose execution plan │ │
│ └──────────────┬───────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────┐ │
│ │ Execute on Base Tables │ │
│ │ customers table │ │
│ └──────────────┬───────────────────────┘ │
│ │ │
│ ▼ │
│ Return Results │
│ │
└─────────────────────────────────────────────────────────┘
Querying Views
Views can be queried exactly like regular tables:
-- Basic query
SELECT * FROM customer_contacts;
-- With WHERE clause
SELECT * FROM customer_contacts
WHERE last_name LIKE 'S%';
-- With JOIN
SELECT
cc.first_name,
cc.last_name,
cc.email,
ms.total_orders
FROM customer_contacts cc
LEFT JOIN monthly_sales ms ON 1=1;
-- With aggregation
SELECT
COUNT(*) as total_contacts,
COUNT(DISTINCT email) as unique_emails
FROM customer_contacts;
Updatable Views
Not all views are updatable. A view is updatable if it meets certain conditions.
Conditions for Updatable Views
┌─────────────────────────────────────────────────────────┐
│ UPDATABLE VIEW REQUIREMENTS │
├─────────────────────────────────────────────────────────┤
│ │
│ ✓ ALLOWED ✗ NOT ALLOWED │
│ ───────────────── ───────────────── │
│ • Single table • Multiple tables (JOIN) │
│ • Simple columns • DISTINCT │
│ • No aggregates • GROUP BY │
│ • No DISTINCT • HAVING │
│ • No GROUP BY • UNION │
│ • No HAVING • Subqueries in SELECT │
│ • No UNION • Aggregate functions │
│ • Window functions │
│ │
└─────────────────────────────────────────────────────────┘
Example: Updatable View
-- Create updatable view
CREATE VIEW active_customers AS
SELECT
customer_id,
first_name,
last_name,
email,
phone
FROM customers
WHERE active = 1;
-- INSERT through view
INSERT INTO active_customers (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');
-- UPDATE through view
UPDATE active_customers
SET phone = '555-1234'
WHERE customer_id = 1;
-- DELETE through view
DELETE FROM active_customers
WHERE customer_id = 100;
WITH CHECK OPTION
-- Prevent updates that violate view condition
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;
Materialized Views
Materialized views store the query results physically, providing significant performance benefits.
Regular View vs Materialized View
┌─────────────────────────────────────────────────────────┐
│ REGULAR VIEW vs MATERIALIZED VIEW │
├─────────────────────────────────────────────────────────┤
│ │
│ REGULAR VIEW MATERIALIZED VIEW │
│ ───────────── ────────────────── │
│ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ View Query │ │ View Query │ │
│ └──────┬───────┘ └──────┬───────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ Execute │ │ Store Results│ │
│ │ Every Time │ │ Physically │ │
│ └──────┬───────┘ └──────┬───────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ Base Tables │ │ Cached Data │ │
│ │ (Slow) │ │ (Fast) │ │
│ └──────────────┘ └──────────────┘ │
│ │ │
│ ┌──────▼───────┐ │
│ │ Refresh │ │
│ │ Periodically │ │
│ └──────────────┘ │
│ │
└─────────────────────────────────────────────────────────┘
Creating Materialized Views
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 total_revenue
FROM order_items
GROUP BY product_id;
-- Refresh materialized view
REFRESH MATERIALIZED VIEW sales_summary;
-- Refresh without locking
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
Oracle:
-- Create materialized view with refresh options
CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
product_id,
COUNT(*) as total_sales,
SUM(quantity) as total_quantity,
SUM(total_amount) as total_revenue
FROM order_items
GROUP BY product_id;
Refresh Strategies
- COMPLETE REFRESH: Rebuild entire view (slow but accurate)
- FAST REFRESH: Incremental updates only (requires materialized view logs)
- ON COMMIT: Refresh after each transaction (always current)
- ON DEMAND: Manual or scheduled refresh (flexible timing)
Indexed Views
Indexed views (SQL Server specific) are materialized views with indexes.
Creating Indexed Views
-- Step 1: Create view with SCHEMABINDING
CREATE VIEW dbo.product_sales_summary
WITH SCHEMABINDING
AS
SELECT
p.product_id,
p.product_name,
COUNT_BIG(*) as order_count,
SUM(oi.quantity) as total_quantity
FROM dbo.products p
INNER JOIN dbo.order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name;
-- Step 2: Create unique clustered index
CREATE UNIQUE CLUSTERED INDEX idx_product_sales
ON dbo.product_sales_summary (product_id);
View Management
Altering Views
-- MySQL/PostgreSQL: CREATE OR REPLACE
CREATE OR REPLACE VIEW customer_contacts AS
SELECT
customer_id,
CONCAT(first_name, ' ', last_name) AS full_name,
email,
phone
FROM customers
WHERE active = 1;
-- SQL Server: ALTER VIEW
ALTER VIEW customer_contacts AS
SELECT
customer_id,
first_name,
last_name,
email
FROM customers;
Dropping Views
-- Drop single view
DROP VIEW customer_contacts;
-- Drop if exists
DROP VIEW IF EXISTS customer_contacts;
-- Drop materialized view
DROP MATERIALIZED VIEW sales_summary;
View Metadata
-- MySQL: List all views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
-- PostgreSQL: View information
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public';
-- SQL Server: List views
SELECT name, create_date
FROM sys.views;
Security with Views
Views are powerful tools for implementing database security.
Row-Level Security
-- View for specific department
CREATE VIEW dept_employees AS
SELECT
employee_id,
first_name,
last_name,
email,
salary
FROM employees
WHERE department_id = 10;
GRANT SELECT ON dept_employees TO dept_manager;
Column-Level Security
-- Hide sensitive columns
CREATE VIEW employee_public AS
SELECT
employee_id,
first_name,
last_name,
email,
department_id
-- Exclude: salary, ssn, bank_account
FROM employees;
GRANT SELECT ON employee_public TO public_users;
Security Architecture
┌─────────────────────────────────────────────────────────┐
│ VIEW SECURITY LAYERS │
├─────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────────────────────────────┐ │
│ │ User Access Layer │ │
│ │ • Different users/roles │ │
│ │ • Different permissions │ │
│ └──────────────┬───────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────┐ │
│ │ View Layer (Security Filter) │ │
│ │ • Row filtering (WHERE) │ │
│ │ • Column filtering (SELECT) │ │
│ │ • Data masking │ │
│ └──────────────┬───────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────┐ │
│ │ Base Tables (Full Data) │ │
│ │ • All rows and columns │ │
│ │ • Sensitive information │ │
│ └──────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────┘
Performance Considerations
View Performance Decision Tree
┌─────────────────────────────────────────────────────────┐
│ VIEW PERFORMANCE DECISION TREE │
├─────────────────────────────────────────────────────────┤
│ │
│ Is query complex/slow? │
│ │ │
│ ┌────┴────┐ │
│ NO YES │
│ │ │ │
│ │ Is data updated frequently? │
│ │ │ │
│ │ ┌────┴────┐ │
│ │ NO YES │
│ │ │ │ │
│ │ │ Can tolerate stale data? │
│ │ │ │ │
│ │ │ ┌────┴────┐ │
│ │ │ NO YES │
│ │ │ │ │ │
│ ▼ ▼ ▼ ▼ │
│ Regular Materialized Indexed Regular View │
│ View View View + Caching │
│ │
└─────────────────────────────────────────────────────────┘
Optimization Tips
-- 1. Index base tables properly
CREATE INDEX idx_customer_active ON customers(active);
CREATE INDEX idx_order_date ON orders(order_date);
-- 2. Use specific columns instead of SELECT *
CREATE VIEW good_view AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE active = 1;
-- 3. Avoid nested views
-- Bad: view1 -> view2 -> view3
-- Good: Direct access to base tables
-- 4. Use materialized views for expensive queries
CREATE MATERIALIZED VIEW expensive_report AS
SELECT
c.customer_id,
COUNT(o.order_id) as total_orders,
SUM(o.total_amount) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
Best Practices
Naming Conventions
-- Use descriptive prefixes
CREATE VIEW v_active_customers AS ... -- v_ prefix
CREATE VIEW vw_monthly_sales AS ... -- vw_ prefix
CREATE MATERIALIZED VIEW mv_sales_summary AS ... -- mv_ prefix
When to Use Views
✓ USE VIEWS WHEN:
- Simplifying complex queries
- Enforcing security policies
- Providing consistent interface
- Abstracting schema changes
- Creating reusable query logic
✗ AVOID VIEWS WHEN:
- Simple single-table queries
- Performance is critical (use materialized)
- Frequent schema changes expected
- Complex nested view hierarchies
Common Pitfalls
-- 1. Avoid SELECT * in views
-- Bad
CREATE VIEW bad_view AS SELECT * FROM customers;
-- Good
CREATE VIEW good_view AS
SELECT customer_id, first_name, last_name FROM customers;
-- 2. Don't nest views too deeply
-- Bad: view1 -> view2 -> view3
-- Good: Direct access or single level
-- 3. Use WITH CHECK OPTION for updatable views
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE active = 1
WITH CHECK OPTION;
Common Use Cases
1. Reporting and Analytics
CREATE VIEW sales_dashboard AS
SELECT
DATE(order_date) as sale_date,
COUNT(*) as total_orders,
SUM(total_amount) as daily_revenue,
AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY
GROUP BY DATE(order_date);
2. Data Simplification
CREATE VIEW customer_info AS
SELECT
customer_id,
CONCAT(first_name, ' ', last_name) as full_name,
email,
phone,
CASE
WHEN total_purchases > 10000 THEN 'VIP'
WHEN total_purchases > 5000 THEN 'Premium'
ELSE 'Standard'
END as customer_tier
FROM customers;
3. Security and Access Control
-- Department-specific view
CREATE VIEW hr_employee_view AS
SELECT
employee_id,
first_name,
last_name,
email,
salary -- Only HR can see
FROM employees;
-- Public view
CREATE VIEW public_employee_view AS
SELECT
employee_id,
first_name,
last_name,
email
-- Salary hidden
FROM employees;
Interview Questions
Basic Questions
Q1: What is a view in SQL?
A view is a virtual table based on the result set of a SQL query. It doesn't store data physically but provides a way to simplify complex queries, enhance security, and present data in a specific format.
Q2: What's the difference between a view and a table?
- Table: Stores data physically, requires storage space
- View: Virtual table, stores only the query definition, minimal storage
Q3: Can you insert data into a view?
Yes, but only if the view is updatable. A view is updatable if it references a single table and doesn't use DISTINCT, GROUP BY, HAVING, or aggregate functions.
Intermediate Questions
Q4: What is a materialized view?
A materialized view stores the query results physically, unlike regular views. It provides better performance for complex queries but requires periodic refresh to stay current.
Q5: Explain WITH CHECK OPTION.
WITH CHECK OPTION ensures that all INSERT and UPDATE operations through a view satisfy the view's WHERE clause. It prevents modifications that would make rows invisible through the view.
Q6: What are the advantages of using views?
- Security: Hide sensitive columns
- Simplification: Abstract complex queries
- Consistency: Standardize data access
- Abstraction: Shield from schema changes
- Reusability: Define once, use many times
Advanced Questions
Q7: How do you optimize view performance?
- Index base tables properly
- Use specific columns instead of SELECT *
- Avoid deep view nesting
- Consider materialized views for complex queries
- Use EXPLAIN to analyze query plans
Q8: Can you create an index on a view?
- Regular views: No direct indexing (indexes on base tables are used)
- Materialized views: Yes, can be indexed
- Indexed views (SQL Server): Yes, requires SCHEMABINDING
Q9: What's the difference between LOCAL and CASCADED CHECK OPTION?
- LOCAL: Checks only the current view's WHERE clause
- CASCADED: Checks the current view and all underlying views' WHERE clauses
Q10: Explain view dependency issues.
When base tables are modified (columns dropped/renamed), dependent views may break. Solutions:
- Use explicit column names (not SELECT *)
- Document dependencies
- Test views after schema changes
Summary
Views are powerful database objects that provide abstraction, security, and query simplification. Choose the right type based on your requirements:
- Regular Views: For security and simplification
- Materialized Views: For performance with complex queries
- Indexed Views: For high-performance queries (SQL Server)
- Updatable Views: For data modification through views
Always consider performance implications, document your views, and follow best practices for naming and structure.