sql_view

SQL Views

A SQL View is a virtual table whose contents are defined by a query. Think of it as a stored query that you can interact with as if it were a regular table. Views do not store data themselves (unless they are materialized views, an advanced topic); instead, they dynamically retrieve and present data from one or more underlying base tables when queried.

This chapter explains how to create, use, update the definition of, and delete views.

SQL CREATE VIEW Statement

A view contains rows and columns, just like a real table. The fields in a view are derived from fields in one or more base tables in the database.

Key Benefits of Using Views:

• Simplicity: Simplify complex queries. Users can query a view without needing to know the underlying table structures or join conditions.
• Security: Restrict data access by exposing only specific columns or rows to users, hiding sensitive information.
• Logical Data Independence: If the underlying table structure changes (e.g., a column is renamed or split), the view definition can sometimes be modified to maintain a consistent interface for applications, minimizing code changes.
• Reusability: Define a common data representation once and use it in multiple queries or applications.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];

view_name: The name you assign to the view.

SELECT ...: The standard SQL SELECT statement that defines the data the view will present. It can include joins, functions, aggregations, etc.

Note: A view always reflects the current data in its underlying tables. The database engine re-executes the view’s SQL query each time the view is queried (unless it’s a materialized view).

SQL CREATE VIEW Examples

Let’s assume we have Products and Categories tables.

Products Table:
- ProductID (INT)
- ProductName (VARCHAR)
- CategoryID (INT)
- UnitPrice (DECIMAL)
- Discontinued (BOOLEAN)

Categories Table:
- CategoryID (INT)
- CategoryName (VARCHAR)

Example 1: View for Active Products

This view lists all products that are not discontinued.

CREATE VIEW ActiveProducts AS
SELECT ProductID, ProductName, UnitPrice, CategoryID
FROM Products
WHERE Discontinued = FALSE; -- Standard SQL for boolean false

You can then query this view like a table:

SELECT * FROM ActiveProducts WHERE UnitPrice > 50;

Example 2: View for Product Details with Category Name

This view joins Products and Categories to show product names along with their category names.

CREATE VIEW ProductDetailsWithCategory AS
SELECT
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    p.UnitPrice
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID;

Querying the view:

SELECT * FROM ProductDetailsWithCategory WHERE CategoryName = 'Electronics';

Example 3: View for Category Sales Summary (Conceptual)

Assuming an OrderDetails table exists, this view could summarize total sales per category.

-- This is a conceptual example; OrderDetails table structure is assumed.
CREATE VIEW CategorySalesSummary AS
SELECT
    c.CategoryName,
    SUM(od.Quantity * od.UnitPrice) AS TotalSales
FROM Categories c
JOIN Products p ON c.CategoryID = p.CategoryID
JOIN OrderDetails od ON p.ProductID = od.ProductID -- Assuming OrderDetails table
GROUP BY c.CategoryName;

Querying the view:

SELECT * FROM CategorySalesSummary ORDER BY TotalSales DESC;

Updating a View’s Definition (CREATE OR REPLACE VIEW)

To modify the definition of an existing view without dropping and recreating it (which could affect permissions), use CREATE OR REPLACE VIEW.

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
...

Example: Add Discontinued status to the ActiveProducts view (though its name implies active, let’s make it explicit or change its purpose). Or, let’s update ProductDetailsWithCategory to include Discontinued status.

CREATE OR REPLACE VIEW ProductDetailsWithCategory AS
SELECT
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    p.UnitPrice,
    p.Discontinued
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID;

Updatability of Views (Data Modification)

You can sometimes use INSERT, UPDATE, or DELETE statements on a view to modify data in the underlying base table(s). However, a view is only updatable if the database can unambiguously determine which base table rows and columns are affected. Generally, a view is updatable if it:

• Is based on a single table (or more, but the DML operation affects only one base table).
• Does not use aggregate functions (SUM(), COUNT(), etc.).
• Does not use GROUP BY or HAVING clauses.
• Does not use DISTINCT.
• Does not use subqueries in the SELECT list that refer to the same table as the FROM clause.
• All NOT NULL columns from the base table that don’t have a default value are included in the view and accessible.

The exact rules can vary between database systems. Attempting to update a non-updatable view will result in an error.

SQL Dropping a View

To remove a view from the database, use the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name;

Example:

DROP VIEW ActiveProducts;

Dropping a view does not affect the data in the underlying tables.

Practical Application Scenarios:

• Creating a CustomerOrderSummary view for a reporting dashboard.
• Providing a PublicUserProfile view that hides sensitive user data like email or password hash.
• Building a HighValueProducts view for targeted marketing campaigns.