Home / SQL / Views

Views

Overview

A VIEW is a virtual table created from the result of a SQL query. Views don't store data themselves but display data from one or more underlying tables.

Views simplify complex queries, provide security by hiding sensitive columns, and create reusable query definitions.

Basic Syntax

CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Examples

Example 1: Simple View

Create basic view

CREATE VIEW ActiveCustomers AS
SELECT customer_id, name, email
FROM Customers
WHERE status = 'Active';

Example 2: View with JOIN

View joining multiple tables

CREATE VIEW CustomerOrders AS
SELECT 
    c.name AS customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;

Example 3: View with Aggregates

View with calculations

CREATE VIEW CustomerSummary AS
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_value
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Using Views

Once created, views are used like regular tables:

Query a view

-- Select from view
SELECT * FROM ActiveCustomers;

-- Filter view
SELECT * FROM CustomerOrders
WHERE total_amount > 100;

-- Join view with tables
SELECT co.*, p.product_name
FROM CustomerOrders co
INNER JOIN OrderItems oi ON co.order_id = oi.order_id
INNER JOIN Products p ON oi.product_id = p.product_id;

Updating Views

Some views can be updated (INSERT, UPDATE, DELETE), but with restrictions:

Updateable view example

-- Simple view (updateable)
CREATE VIEW SimpleCustomerView AS
SELECT customer_id, name, email
FROM Customers;

-- Can update
UPDATE SimpleCustomerView
SET email = 'newemail@example.com'
WHERE customer_id = 101;

Note: Views with JOINs, aggregates, or DISTINCT are generally not updateable.

Dropping Views

Remove a view:

Drop view

DROP VIEW view_name;

-- Or with IF EXISTS
DROP VIEW IF EXISTS ActiveCustomers;

Modifying Views

Alter an existing view (syntax varies by database):

Alter view

-- MySQL
CREATE OR REPLACE VIEW ActiveCustomers AS
SELECT customer_id, name, email, phone
FROM Customers
WHERE status = 'Active';

-- SQL Server
ALTER VIEW ActiveCustomers AS
SELECT customer_id, name, email, phone
FROM Customers
WHERE status = 'Active';

Benefits of Views

Simplify queries: Hide complexity of underlying queries
Security: Hide sensitive columns from users
Reusability: Define once, use many times
Consistency: Standardized way to access data
Abstraction: Changes to base tables don't affect views (if structure preserved)

View vs Table

Understanding the difference:

Property	View	Table
Data storage	No data stored	Data stored
Query execution	Runs query each time	Direct data access
Updates	Limited (depends on query)	Always updateable
Performance	Can be slower	Direct access

Best Practices

Use descriptive names: Make view purpose clear
Document complex views: Comment on why view exists
Consider performance: Complex views can be slow
Use for security: Restrict access to sensitive data
Keep simple when possible: Simpler views are easier to maintain

Common Use Cases

Providing simplified access to complex queries
Hiding sensitive columns from users
Creating reusable query definitions
Presenting aggregated data
Simplifying reporting queries

Next Steps

Learn about defining column types with Data Types, or explore creating indexes for performance in CREATE INDEX.

← Create Index Data Types →