Creating and Using Views
A view is a virtual table based on a SQL query. It does not store data itself — instead, it stores the query definition and runs it each time the view is referenced. Views simplify complex queries and provide a layer of abstraction over your tables.
Think of a view as a saved query that you can use just like a table. You can SELECT from it, join with it, and even filter it with WHERE.
- Simplification: Wrap complex joins and calculations into a simple table-like interface
- Security: Expose only certain columns to users without granting access to underlying tables
- Consistency: Ensure everyone uses the same query logic for common calculations
- Maintainability: Change the view definition once and all dependent queries are updated
-- Create a view for active users
CREATE VIEW active_users AS
SELECT id, username, email, age
FROM users
WHERE is_active = TRUE;
-- Use the view just like a table
SELECT * FROM active_users;
SELECT * FROM active_users WHERE age > 25;
-- Create a view with a join
CREATE VIEW customer_orders AS
SELECT
c.name AS customer_name,
c.email,
COUNT(o.id) AS total_orders,
SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email;
-- Query the view
SELECT * FROM customer_orders
WHERE total_orders > 0
ORDER BY total_spent DESC;- Views do not improve query performance — they run the underlying query each time. For performance gains, consider materialized views (available in PostgreSQL) which cache the results.
Updating and Dropping Views
You can modify a view definition using CREATE OR REPLACE VIEW, which updates the view without dropping and recreating it. To remove a view, use DROP VIEW.
Some simple views are updatable, meaning you can run INSERT, UPDATE, and DELETE on the view and the changes will be applied to the underlying table. However, views with joins, aggregates, or GROUP BY are generally not updatable.
-- Update a view definition
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, age, join_date
FROM users
WHERE is_active = TRUE;
-- List all views in the database (MySQL)
SHOW FULL TABLES WHERE Table_type = 'VIEW';
-- Updatable view: simple views allow data modification
-- This works because the view is a simple filter on one table
UPDATE active_users
SET age = 30
WHERE username = 'alice';
-- Drop a view
DROP VIEW IF EXISTS active_users;
DROP VIEW IF EXISTS customer_orders;
-- Create a view to hide sensitive data
CREATE VIEW public_users AS
SELECT id, username, join_date
FROM users; -- email and other sensitive fields are excluded- Use views to implement column-level security. By granting users access to a view instead of the underlying table, you can control exactly which columns they can see.