SQL Views

A view is a virtual table based on the result set of an SQL query. Unlike a physical base table, a view does not store data on disk by default. Instead, it contains rows and columns dynamic in nature, generated dynamically from referenced tables whenever the view is queried.

Views act as a saved query definition that users and applications can query as if it were a real table.

Why Use Views?

1. Simplifying Complex Queries: Encapsulates complicated multi-table JOIN statements, aggregations, and subqueries into a clean, reusable database object.
2. Data Security & Row/Column Access Control: Restricts user access to specific columns or rows of sensitive tables without exposing the underlying table structure.
3. Data Independence & Schema Abstraction: Provides a stable interface layer for reporting tools and applications. If underlying physical table structures change, you can update the view definition without breaking application queries.
4. Consistency: Guarantees that business logic (such as calculating net revenue or active customer status) is computed consistently across all reports.

Basic Syntax & Examples

1. Creating a View (CREATE VIEW)

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

Example: Hiding Sensitive Information

Suppose you have an Employees table containing salary data, social security numbers, and contact information. You can expose a public directory view that omits sensitive attributes:

CREATE VIEW EmployeeDirectory AS
SELECT EmployeeID, FirstName, LastName, Department, WorkEmail
FROM Employees
WHERE Status = 'Active';

Now, non-admin users can query EmployeeDirectory directly:

SELECT * FROM EmployeeDirectory
WHERE Department = 'Engineering';

Example: Encapsulating Complex Joins

Instead of writing a multi-table JOIN query repeatedly for daily sales reporting:

CREATE VIEW DailySalesSummary AS
SELECT 
    o.OrderID,
    c.CustomerName,
    p.ProductName,
    o.Quantity,
    (o.Quantity * p.UnitPrice) AS TotalAmount,
    o.OrderDate
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID;

Modifying and Updating Views

1. Updating a View Definition (CREATE OR REPLACE VIEW)

To update the query logic behind an existing view without dropping permissions:

-- PostgreSQL / MySQL / Oracle
CREATE OR REPLACE VIEW EmployeeDirectory AS
SELECT EmployeeID, FirstName, LastName, Department, WorkEmail, Phone
FROM Employees
WHERE Status = 'Active';

-- SQL Server (T-SQL)
ALTER VIEW EmployeeDirectory AS
SELECT EmployeeID, FirstName, LastName, Department, WorkEmail, Phone
FROM Employees
WHERE Status = 'Active';

2. Updatable Views & WITH CHECK OPTION

Under certain conditions, INSERT, UPDATE, or DELETE operations performed directly on a view will pass through and modify the underlying base table.

Conditions for Updatable Views:

• The view must reference only one base table in the FROM clause.
• The view must not contain aggregate functions (SUM, AVG, COUNT), GROUP BY, HAVING, DISTINCT, or UNION.
• The view must not use window functions or calculated columns.

Enforcing Integrity with WITH CHECK OPTION

The WITH CHECK OPTION clause prevents users from inserting or updating rows through a view if those rows do not satisfy the view's WHERE condition:

CREATE VIEW HighValueCustomers AS
SELECT CustomerID, CustomerName, CreditLimit
FROM Customers
WHERE CreditLimit >= 50000
WITH CHECK OPTION;

If a user tries to insert a customer with a CreditLimit of 30000 through HighValueCustomers, the database engine rejects the insert with a violation error.

Materialized Views (Physical Caching)

In standard views, the underlying query runs every time the view is called. For massive data warehouses, running complex aggregations repeatedly can degrade server performance.

A Materialized View solves this by executing the query once and physically storing the result set on disk like a table.

-- PostgreSQL / Oracle
CREATE MATERIALIZED VIEW mv_monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', OrderDate) AS SalesMonth,
    SUM(TotalAmount) AS TotalRevenue,
    COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY DATE_TRUNC('month', OrderDate);

• Refreshing Data: Because data is cached, it becomes stale as base tables change. You must refresh materialized views periodically:

REFRESH MATERIALIZED VIEW mv_monthly_sales_summary;

Dropping a View

To permanently remove a view from the database schema:

DROP VIEW IF EXISTS EmployeeDirectory;

Note: Dropping a view removes only the virtual definition; it has zero impact on the data stored inside the underlying base tables.