What Is a View in SQL?
Learn what a SQL view is, how it differs from a materialized view, and why views simplify queries and improve data security, with practical examples.
Expected Interview Answer
A view is a saved, named SELECT query that behaves like a virtual table — it stores no data of its own (unless materialized) and instead re-runs its underlying query each time it is referenced.
Views let you encapsulate complex joins, filters, or aggregations behind a simple name, so users and applications can query `SELECT * FROM sales_summary_view` instead of repeating a long query. Because a regular view has no stored data, it always reflects the live state of its underlying tables at query time. Views are also useful for restricting access, exposing only certain columns or rows to a group of users without granting direct table access. A materialized view, in contrast, does persist its result set physically and must be refreshed on a schedule or trigger, trading freshness for read speed.
- Simplifies repeated complex queries behind a simple name
- Provides a security layer by exposing only permitted columns/rows
- Keeps results live since a standard view re-runs its query each time
- Materialized views trade freshness for faster reads on expensive queries
- Decouples application code from underlying schema changes
AI Mentor Explanation
A view is like a standing scoreboard formula a curator sets up once, such as 'runs minus wickets lost', that recalculates automatically from the live match feed whenever anyone glances at it. You never store the computed number itself; you just re-run the same formula against whatever the scoreboard shows right now.
Step-by-Step Explanation
Step 1
Define the underlying query
A view is created with CREATE VIEW name AS SELECT ... wrapping any valid query.
Step 2
Query it like a table
Once created, you SELECT from the view name just as you would a regular table.
Step 3
It re-executes live
A standard view has no stored rows; the underlying SELECT runs fresh every time the view is queried.
Step 4
Use it for security or simplicity
Grant access to the view instead of the base tables to hide columns/rows, or use it to hide a complex join.
Step 5
Consider materialization for cost
If the underlying query is expensive and freshness can lag, a materialized view caches the result and is refreshed on a schedule.
What Interviewer Expects
- Defines a view as a saved/named query, not stored data
- Explains that a standard view always reflects live underlying data
- Mentions using views to restrict column/row access
- Distinguishes a materialized view from a regular view
- Knows a view can simplify a complex repeated query
Common Mistakes
- Believing a regular view physically stores a copy of the data
- Confusing a view with a temporary table
- Forgetting that updating through a view has limits (joins, aggregates)
- Not knowing materialized views require manual/scheduled refresh
Best Answer (HR Friendly)
“A view is a saved query that acts like a virtual table — it doesn't store data itself, it just re-runs its underlying query whenever you look at it, which is useful for simplifying complex queries and controlling what data different users can see.”
Code Example
CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';
-- Query the view like a table
SELECT * FROM active_customers WHERE name LIKE 'A%';Follow-up Questions
- What is a materialized view and how does it differ from a regular view?
- Can you insert or update data through a view?
- How do views help enforce column-level security?
- What happens to a view if its underlying table's schema changes?
- Can a view be built on top of another view?
MCQ Practice
1. What does a standard SQL view store?
A regular view stores only the SELECT definition and re-runs it each time it's queried.
2. Why might you query a view instead of the base table?
Views encapsulate complexity and can expose only permitted columns or rows.
3. What distinguishes a materialized view from a regular view?
Materialized views persist their result set and require a refresh to stay current, unlike regular views.
Flash Cards
What is a view? — A saved, named SELECT query that acts like a virtual table.
Does a view store data? — No (unless materialized) — it re-runs its query each time.
Why use a view? — To simplify complex queries and restrict column/row access.
Materialized view — A view that physically stores results and needs periodic refresh.