CodeCapsule

SQL Views

by

Here is a clear, practical, and complete guide to SQL Views, including syntax, behavior, updatable vs. non-updatable views, and examples for MySQL, PostgreSQL, SQL Server, and Oracle.

✅ What Is a SQL VIEW?

A VIEW is a virtual table created from a SQL query.

A view:

  • Does not store data (unless it’s a materialized view)

  • Is used like a table: SELECT * FROM view_name;

  • Helps simplify complex queries

  • Improves security by restricting sensitive columns

  • Provides abstraction and cleaner code

🔹 Basic Syntax — CREATE VIEW

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

🔥 Example: Simple View

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

Use it like a table:

SELECT * FROM active_users;

🔥 Example: View with JOIN

CREATE VIEW user_orders AS
SELECT u.id AS user_id,
u.name,
o.id AS order_id,
o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

🔥 Example: View for Aggregation

CREATE VIEW monthly_sales AS
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY month;

🟦 CREATE OR REPLACE VIEW (PostgreSQL, MySQL 8+, SQL Server)

CREATE OR REPLACE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';

🟥 Update a View (SQL Server)

ALTER VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';

🔥 Dropping a View

DROP VIEW view_name;

Safe drop:

DROP VIEW IF EXISTS view_name;

🔥 Updatable Views (Important!)

Some views allow INSERT, UPDATE, and DELETE on them.

A view is updatable if:

  • Based on a single table

  • No GROUP BY

  • No DISTINCT

  • No aggregate functions

  • No subqueries

  • No UNION

Example (updatable):

CREATE VIEW user_basic AS
SELECT id, name, email
FROM users;

Example (NOT updatable):

CREATE VIEW top_customers AS
SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id;

🔥 WITH CHECK OPTION (Ensures Data Integrity)

Prevents inserting or updating rows that don’t satisfy the view’s WHERE clause.

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = TRUE
WITH CHECK OPTION;

Now this will FAIL:

INSERT INTO active_users (name, active) VALUES ('Sam', FALSE);

🌟 MATERIALIZED VIEWS (PostgreSQL, Oracle, SQL Server Indexed Views)

Materialized views store the query result physically.

  • Faster for large aggregations

  • Must be refreshed

PostgreSQL Example:

CREATE MATERIALIZED VIEW monthly_sales_mv AS
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY month;

Refresh:

REFRESH MATERIALIZED VIEW monthly_sales_mv;

📌 Advantages of Views

✔ Security

Expose only required columns:

CREATE VIEW user_public AS
SELECT name, city FROM users;

✔ Simplify complex queries

Use a short SELECT instead of a long JOIN.

✔ Reusable logic

Write once → use everywhere.

✔ Reduce code duplication

Business logic centralized in one place.

📌 Disadvantages of Views

❌ Can hide performance problems
❌ Harder to index (views themselves aren’t indexed)
❌ Complex nested views become unreadable
❌ Not all views are updatable

🏎 Best Practices

✔ Prefer CREATE OR REPLACE VIEW for safe updates
✔ Use meaningful names (e.g., vw_active_users)
✔ Avoid nested views unless necessary
✔ Use materialized views for expensive calculations
✔ Document whether a view is updatable or not
✔ Index underlying tables for performance

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *