SQL - CREATE VIEW with Examples
• Views act as virtual tables that store SQL queries rather than data, providing abstraction layers that simplify complex queries and enhance security by restricting direct table access
Key Insights
• Views act as virtual tables that store SQL queries rather than data, providing abstraction layers that simplify complex queries and enhance security by restricting direct table access • Materialized views physically store query results for performance optimization, while standard views execute their underlying query each time they’re accessed • Views support updateability under specific conditions—single table views without aggregations or joins typically allow INSERT, UPDATE, and DELETE operations
Understanding SQL Views
A view is a stored SQL query that appears as a virtual table. When you query a view, the database executes the underlying SELECT statement and returns results as if you were querying a physical table. Views don’t store data themselves—they’re saved queries that reference base tables.
-- Basic view syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Views serve multiple purposes: simplifying complex queries, enforcing security policies, maintaining backward compatibility when schema changes occur, and providing consistent interfaces to underlying data structures.
Creating Basic Views
Start with a simple view that filters and presents specific columns from a table:
-- Create a customers table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
credit_limit DECIMAL(10,2),
status VARCHAR(20)
);
-- Create a view showing only active customers
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE status = 'ACTIVE';
-- Query the view
SELECT * FROM active_customers;
This view hides sensitive information like credit limits and phone numbers while filtering for active customers only. Application code queries active_customers without knowing the underlying complexity or security rules.
Views with Joins
Views excel at encapsulating complex joins, making them reusable across multiple queries:
-- Create related tables
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
product_name VARCHAR(100),
quantity INT,
unit_price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
-- Create a comprehensive order view
CREATE VIEW order_details AS
SELECT
o.order_id,
o.order_date,
c.first_name,
c.last_name,
c.email,
oi.product_name,
oi.quantity,
oi.unit_price,
(oi.quantity * oi.unit_price) AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id;
-- Use the view
SELECT * FROM order_details
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC;
Views with Aggregations
Create analytical views that pre-compute aggregations for reporting:
-- Customer spending summary view
CREATE VIEW customer_spending_summary AS
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.total_amount) AS lifetime_value,
AVG(o.total_amount) AS avg_order_value,
MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;
-- Query high-value customers
SELECT * FROM customer_spending_summary
WHERE lifetime_value > 10000
ORDER BY lifetime_value DESC;
Replacing and Modifying Views
Use CREATE OR REPLACE VIEW to update view definitions without dropping them:
-- Update the active_customers view to include phone numbers
CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers
WHERE status = 'ACTIVE';
For databases that don’t support CREATE OR REPLACE, drop and recreate:
DROP VIEW IF EXISTS active_customers;
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers
WHERE status = 'ACTIVE';
Updatable Views
Views can be updatable if they meet specific criteria. Generally, a view is updatable when it:
- References a single base table
- Includes all NOT NULL columns without defaults
- Contains no aggregate functions, DISTINCT, GROUP BY, or UNION clauses
-- Create an updatable view
CREATE VIEW customer_contacts AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers;
-- Insert through the view
INSERT INTO customer_contacts (customer_id, first_name, last_name, email, phone)
VALUES (101, 'John', 'Doe', 'john.doe@example.com', '555-0100');
-- Update through the view
UPDATE customer_contacts
SET email = 'john.d@example.com'
WHERE customer_id = 101;
-- Delete through the view
DELETE FROM customer_contacts
WHERE customer_id = 101;
Views with CHECK OPTION
Prevent updates that would make rows invisible to the view using WITH CHECK OPTION:
CREATE VIEW premium_customers AS
SELECT customer_id, first_name, last_name, credit_limit
FROM customers
WHERE credit_limit >= 5000
WITH CHECK OPTION;
-- This succeeds
UPDATE premium_customers
SET credit_limit = 6000
WHERE customer_id = 1;
-- This fails - would make the row disappear from the view
UPDATE premium_customers
SET credit_limit = 3000
WHERE customer_id = 1;
Materialized Views
Materialized views physically store query results, trading storage for query performance. Syntax varies by database:
-- PostgreSQL materialized view
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
-- Refresh the materialized view
REFRESH MATERIALIZED VIEW monthly_sales_summary;
-- Oracle syntax
CREATE MATERIALIZED VIEW monthly_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT
TRUNC(order_date, 'MM') AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY TRUNC(order_date, 'MM');
Indexed Views (SQL Server)
SQL Server allows creating indexes on views for performance optimization:
-- Create view with SCHEMABINDING
CREATE VIEW dbo.product_sales_summary
WITH SCHEMABINDING
AS
SELECT
product_name,
COUNT_BIG(*) AS sale_count,
SUM(quantity) AS total_quantity
FROM dbo.order_items
GROUP BY product_name;
-- Create clustered index on the view
CREATE UNIQUE CLUSTERED INDEX IX_product_sales
ON dbo.product_sales_summary (product_name);
Security and Access Control
Views provide row-level and column-level security:
-- Create role-specific views
CREATE VIEW sales_team_view AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days';
CREATE VIEW executive_view AS
SELECT
DATE_TRUNC('quarter', order_date) AS quarter,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('quarter', order_date);
-- Grant access to specific roles
GRANT SELECT ON sales_team_view TO sales_role;
GRANT SELECT ON executive_view TO executive_role;
Performance Considerations
Views add a layer of abstraction that can impact performance. The database optimizer typically merges view queries with outer queries, but complex views with multiple joins or aggregations may create optimization challenges.
Monitor execution plans and consider materialized views for frequently accessed complex queries. Avoid nesting views deeply—each layer adds complexity to query optimization.
-- Check execution plan
EXPLAIN SELECT * FROM order_details
WHERE order_date >= '2024-01-01';
Use views for abstraction and security, not as a substitute for proper indexing and query optimization.