Definition
- View – a named, stored
SELECTquery that behaves like a virtual table, holding no rows of its own. - Simplification – wraps complex multi-join, multi-filter logic so callers write one clean
SELECTagainst the view. - Security – exposes only chosen columns and rows, granting
SELECTon the view while denying direct table access. - Abstraction – callers depend on the view shape, not the physical table layout, so you can refactor base tables without breaking consumers.
- Reusability – the definition lives in one place, shared by every service, report, and ad-hoc query.
- Consistent reporting – a named view guarantees every consumer computes a metric the same way.
A view is like a pre-approved National Bank of India report template. Instead of every clerk re-deriving "active accounts with balance over ten thousand" from raw tables each time, the bank defines that report once, names it, and everyone just runs the named report. Tellers see only the columns the template exposes, never the sensitive raw columns behind it, so the view also acts as a controlled window into the data.
Understanding the Concepts
What is a view in SQL?
A view is a named, stored SELECT query that the database saves as a definition. It holds no rows of its own. Every time you query the view, the database expands the view's query into yours and runs it against the live base tables.
-- Create a view at National Bank of India
CREATE VIEW active_premium_accounts AS
SELECT
a.account_id,
a.holder_name,
a.account_type,
a.balance
FROM accounts a
WHERE a.status = 'ACTIVE'
AND a.balance >= 100000.00;
Callers now write a simple query instead of repeating the join and filter logic every time.
-- Any service or report uses the view directly
SELECT account_id, holder_name, balance
FROM active_premium_accounts
ORDER BY balance DESC;
Why do we need views?
Views exist because real-world databases have dozens of tables, complex joins, and business rules that would otherwise be duplicated across every query. Without views, every service re-derives the same logic, and a rule change means hunting through every copy. Views centralize the logic once.
-- Without a view: every report repeats this
SELECT a.account_id, a.holder_name, a.balance,
b.branch_name, b.city
FROM accounts a
JOIN branches b ON a.branch_id = b.branch_id
WHERE a.status = 'ACTIVE'
AND a.account_type = 'SAVINGS'
AND a.balance >= 100000.00;
-- With a view: define once, use everywhere
CREATE VIEW premium_savings AS
SELECT a.account_id, a.holder_name, a.balance,
b.branch_name, b.city
FROM accounts a
JOIN branches b ON a.branch_id = b.branch_id
WHERE a.status = 'ACTIVE'
AND a.account_type = 'SAVINGS'
AND a.balance >= 100000.00;
SELECT * FROM premium_savings WHERE city = 'DELHI';
How does a view work internally?
When you run SELECT * FROM my_view WHERE x = 1, the database replaces my_view with its stored query definition and merges the two queries. This is called view merging or view expansion. The optimizer then plans the combined query as if you had written it inline.
User query: SELECT * FROM premium_savings WHERE city = 'DELHI'
|
View expansion
|
Expanded: SELECT ... FROM accounts a
JOIN branches b ON ...
WHERE a.status = 'ACTIVE'
AND a.account_type = 'SAVINGS'
AND a.balance >= 100000.00
AND b.city = 'DELHI'
Because the view stores only the text definition, it always reflects current data and uses no extra disk space.
How do views enforce security?
Views provide column-level and row-level security. You grant SELECT on the view and deny direct access to the base tables. The view exposes only the columns and rows you choose.
-- Hide sensitive columns from the reporting team
CREATE VIEW account_summary AS
SELECT
'XXXX' || RIGHT(account_id, 4) AS masked_account,
holder_name,
account_type,
balance
FROM accounts
WHERE status = 'ACTIVE';
-- Grant access to the view, not the base table
GRANT SELECT ON account_summary TO reporting_team;
REVOKE SELECT ON accounts FROM reporting_team;
Now the reporting team can run SELECT * FROM account_summary but cannot see the full account number, PAN, or inactive accounts.
How do views provide abstraction and logical independence?
Callers depend on the view's column names and types, not the physical table layout. If you refactor, rename, or split a base table, you update the view definition and every consumer continues to work unchanged.
-- Original: single accounts table
CREATE VIEW customer_balances AS
SELECT account_id, holder_name, balance FROM accounts;
-- After refactoring: accounts split into two tables
CREATE OR REPLACE VIEW customer_balances AS
SELECT a.account_id, c.full_name AS holder_name, a.balance
FROM account_ledger a
JOIN customers c ON a.customer_id = c.customer_id;
-- Every consumer still runs the same query
SELECT * FROM customer_balances WHERE account_id = 'SAV2001';
Are views updatable?
Simple views based on a single table with no aggregation, DISTINCT, GROUP BY, or UNION can be updatable. The database maps the INSERT, UPDATE, or DELETE on the view back to the base table.
-- Simple, updatable view
CREATE VIEW active_accounts AS
SELECT account_id, holder_name, balance, status
FROM accounts
WHERE status = 'ACTIVE'
WITH CHECK OPTION;
-- This update goes through to the base table
UPDATE active_accounts
SET balance = balance + 5000.00
WHERE account_id = 'SAV2001';
-- WITH CHECK OPTION prevents making a row escape the view
UPDATE active_accounts
SET status = 'FROZEN'
WHERE account_id = 'SAV2001';
-- ERROR: new row violates check option for view "active_accounts"
Complex views with joins or aggregations are read-only unless you add INSTEAD OF triggers.
What are the limitations of views?
Views do not inherently improve performance because the underlying query runs on every access. They cannot accept parameters like a stored procedure. Complex views can confuse the optimizer if view merging fails, leading to sub-optimal plans. And debugging a chain of views stacked on views can be difficult.
-- Anti-pattern: stacking views on views
CREATE VIEW v1 AS SELECT ... FROM accounts WHERE ...;
CREATE VIEW v2 AS SELECT ... FROM v1 JOIN branches ON ...;
CREATE VIEW v3 AS SELECT ... FROM v2 WHERE ...;
-- Debugging v3 requires tracing through v2 and v1
For expensive aggregations that are read often, consider a materialized view instead of a plain view.
What are common mistakes with views?
A common mistake is assuming a view caches data and improves query speed. It does not. Every access re-runs the underlying query.
-- Wrong assumption: "this view is fast because it is pre-computed"
CREATE VIEW monthly_totals AS
SELECT branch_id, DATE_TRUNC('month', txn_date) AS month,
SUM(amount) AS total
FROM transactions
GROUP BY branch_id, DATE_TRUNC('month', txn_date);
-- Every SELECT from monthly_totals scans the full transactions table
SELECT * FROM monthly_totals WHERE month = '2024-01-01';
-- This is NOT cached. Use a materialized view if you need caching.
Another mistake is granting direct table access alongside the view, which defeats the security purpose.
When should you use views?
Use views when you need to simplify a frequently used complex query, enforce column-level or row-level security, provide a stable interface that hides physical schema changes, or ensure consistent metric definitions across multiple consumers. Do not use views as a performance optimization tool. For that, use materialized views, proper indexes, or caching.
-- Good use: consistent metric definition for all reports
CREATE VIEW branch_performance AS
SELECT
b.branch_id,
b.branch_name,
COUNT(a.account_id) AS total_accounts,
SUM(a.balance) AS total_deposits
FROM branches b
LEFT JOIN accounts a ON b.branch_id = a.branch_id
AND a.status = 'ACTIVE'
GROUP BY b.branch_id, b.branch_name;
-- Every dashboard, report, and service uses the same definition
SELECT * FROM branch_performance ORDER BY total_deposits DESC;
How do you call a view from Java?
From the application layer, a view is queried exactly like a table. Rajesh writes a JDBC or Spring Data query against the view name.
package com.bank.repository;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class AccountSummaryRepository {
private final Connection connection;
public AccountSummaryRepository(Connection connection) {
this.connection = connection;
}
public List<String> getPremiumAccounts(BigDecimal minBalance) throws SQLException {
String sql = "SELECT masked_account, holder_name, balance "
+ "FROM account_summary "
+ "WHERE balance >= ? ORDER BY balance DESC";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setBigDecimal(1, minBalance);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(rs.getString("holder_name")
+ " - " + rs.getBigDecimal("balance"));
}
}
}
return results;
}
}
Output:
Rajesh Kumar - 250000.00
Priya Sharma - 180000.00
Visual Overview
┌─────────────────────────────────────────────┐ │ How a View Works │ ├─────────────────────────────────────────────┤ │ │ │ CREATE VIEW active_premium_accounts AS │ │ SELECT ... FROM accounts WHERE ... │ │ │ │ │ ▼ │ │ ┌─────────────────────┐ │ │ │ View Definition │ (stored as text) │ │ │ No rows stored │ │ │ └─────────┬───────────┘ │ │ │ │ │ SELECT * FROM active_premium_accounts │ │ │ │ │ ▼ │ │ ┌─────────────────────┐ │ │ │ View Expansion │ │ │ │ Merge user query │ │ │ │ with view query │ │ │ └─────────┬───────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────┐ │ │ │ Execute against │ │ │ │ live base tables │ │ │ │ (always fresh) │ │ │ └─────────────────────┘ │ │ │ ├─────────────────────────────────────────────┤ │ View Security Model │ │ │ │ Reporting Team ──► account_summary (view) │ │ ✓ SELECT │ │ │ ▼ │ │ accounts (table) │ │ ✗ Direct access denied │ └─────────────────────────────────────────────┘
Key Takeaway
A view is a named, stored SELECT query that behaves like a virtual table. It holds no rows of its own and instead runs its underlying query whenever it is referenced. Its purposes cover several distinct needs. It simplifies queries by wrapping a complex multi-join, multi-filter statement once so callers write a single clean SELECT against the view. It enables reuse because the logic is defined in one place and shared by many services and reports, so a rule change happens once.
Views enforce security by exposing only chosen columns and rows. You can hide sensitive columns like a full account number or PAN, restrict rows with a WHERE, and grant users access to the view while denying access to the base tables, giving column-level and row-level security. They provide abstraction and logical independence because callers depend on the view's stable shape rather than the physical table layout, so you can refactor base tables and just update the view definition.
At the National Bank of India, the accounts table mixed sensitive columns with ordinary ones, and several dashboards kept re-deriving "active premium accounts" with slightly different filters, producing conflicting numbers. Pankaj defined one view, active_premium_accounts, that filtered to active premium rows and exposed only a masked account number, tier, and balance, then granted reporting staff access to the view alone. That single change simplified every report, guaranteed consistent figures, and ensured no report could reach the PAN or full account number.
The senior nuances are that a plain view is recomputed on each access so it is always fresh but not a performance shortcut, that simple single-table views can be updatable while complex ones need INSTEAD OF triggers, that WITH CHECK OPTION stops writes that would escape the view's filter, and that when an expensive aggregation must be fast you reach for a materialized view instead.
Remember one sentence: a view is a stored query acting as a virtual table that simplifies, reuses, secures, and abstracts access to data without duplicating it.
---