What is a VIEW in SQL?
A VIEW is a virtual table in SQL.
- It’s a saved SQL query that you can treat like a table when writing queries.
- A VIEW does not store actual data — it just stores the query definition.
- When you query a VIEW, the database runs the underlying SQL and gives you the result.
In short:
VIEW = Named, saved SQL query.
How to Create a View
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
Example:
CREATE VIEW ActiveCustomers AS
SELECT customer_id, name
FROM Customers
WHERE status = 'Active';
Now you can:
SELECT * FROM ActiveCustomers;
Just like a table!
Key Properties of a VIEW
| Feature | VIEW |
|---|---|
| Virtual table | Does not store data; runs the underlying query each time. |
| Based on tables | Built from one or more existing tables or other views. |
| Read-only (by default) | Most views cannot be updated (some can under restrictions). |
| Simplifies queries | Hides complexity (e.g., joins, filters) behind a name. |
| Secures data | Can show only certain columns to users, hiding others. |
How is a VIEW Different from a TABLE?
| Aspect | TABLE | VIEW |
|---|---|---|
| Stores data | Yes — actual stored rows. | No — just a saved query. |
| Physical storage | Takes space on disk (rows, indexes). | Minimal storage — just query definition. |
| Query performance | Very fast (data is ready). | Can be slower (query is executed each time). |
| Updatable | Yes — fully read/write. | Sometimes read-only (depends on the view). |
| Independent | Exists on its own. | Depends on underlying tables. |
Why Use Views?
✅ Simplify Complex Queries
- Instead of writing a huge join or filter every time, wrap it in a view.
✅ Security
- Limit access to sensitive columns — users query the view instead of the base table.
✅ Logical Layer
- Present a different logical structure without changing the physical database.
✅ Reuse
- Create standard business rules — like “Active Customers” — and reuse them.
Example Scenario
Without view:
SELECT customer_id, name
FROM Customers
WHERE status = 'Active' AND region = 'North';
You write this every time.
With view:
CREATE VIEW NorthActiveCustomers AS
SELECT customer_id, name
FROM Customers
WHERE status = 'Active' AND region = 'North';
-- Later:
SELECT * FROM NorthActiveCustomers;
✅ Much simpler.
But Wait: What About Materialized Views?
Some databases (PostgreSQL, Oracle) support Materialized Views:
- They store the query result on disk.
- Faster to query (precomputed).
- Must be refreshed manually or on schedule.
📝 VIEW = runs fresh query every time.
📝 MATERIALIZED VIEW = stores and refreshes data periodically.
In Short
View = virtual table (saved query) — no data stored.
Table = real table (stores rows of data).