Sadiul Hakim

Posted on Oct 1

Database VIEW tutorial

#sql #database #view

1. What is a Database View?

A view is a virtual table created from the result of a SQL query.

It doesn’t store data itself (except in materialized views).
Instead, it pulls data dynamically from one or more tables whenever you query it.
Think of it as a saved query you can treat like a table.

2. When to Use Views and When Not To

Use Views when:

You want to simplify complex queries (hide JOINs, filters, aggregates).
You want consistent business logic across the app (e.g., "active customers").
You need to restrict access (user sees only part of a table).
You want to provide a layer of abstraction so schema changes don’t break apps.

Avoid Views when:

Performance is critical, and the view query is too heavy (every query executes underlying SQL).
You need frequent updates — some views are not updateable.
You want caching — a normal view doesn’t store data (use materialized view instead).

3. Why Are Views Useful?

Security: Expose only certain columns/rows to users.
Maintainability: Centralize logic — change view once instead of every query in code.
Reusability: Developers just query the view instead of writing complex SQL.
Abstraction: Protects applications from schema changes.

4. Is a View a Type of Table?

No, a view is not a physical table.
It’s a virtual table — acts like a table when queried but doesn’t store data.
Materialized View (in some databases like Oracle, PostgreSQL) is closer to a table because it stores data physically and can be refreshed.

5. Why Use a View When We Have Procedures and Other Solutions?

Procedures are executed, but you cannot directly join them in a query.
Views integrate into SQL queries as if they are tables.
Example:

  SELECT * FROM ActiveCustomers JOIN Orders ON ...;

→ You can’t do this with a stored procedure, but you can if ActiveCustomers is a view.

6. How Does a View Stay in Sync with Underlying Tables?

A normal view always queries live data — so it’s automatically in sync.
A materialized view takes a snapshot of data and requires refresh (ON DEMAND or scheduled).

7. How to Create a View

General SQL syntax:

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE condition;

To remove:

DROP VIEW view_name;

8. Example: Simple View on a Single Table

Suppose we have a Customers table:

CREATE VIEW ActiveCustomers AS
SELECT id, name, email
FROM Customers
WHERE status = 'ACTIVE';

Usage:

SELECT * FROM ActiveCustomers;

9. Example: View with Join

Suppose Orders and Customers:

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

Usage:

SELECT * FROM CustomerOrders WHERE total_amount > 1000;

10. Can We Update a View?

Yes, if it’s a simple view (single table, no aggregates, no DISTINCT, no GROUP BY, no joins).
Updating the view updates the underlying table.

Example:

UPDATE ActiveCustomers
SET email = 'newmail@example.com'
WHERE id = 1;

This changes the Customers table too.

No, if it’s a complex view (joins, aggregates, subqueries). → In that case, DBMS may block updates or require INSTEAD OF triggers (like in SQL Server, Oracle).

11. Extra Tips

Indexed Views (SQL Server) or Materialized Views (PostgreSQL/Oracle) can boost performance.
You can grant permissions on a view without exposing underlying tables.
Views are great for reporting and analytics dashboards.

Summary:
A view is a powerful SQL tool: a saved query that behaves like a virtual table. Use it to simplify queries, secure data, and enforce consistency — but be mindful of performance and update limitations.

DEV Community