SQL - Create Views

SQL - Modern View Creation and Usage

What is an SQL View?

In SQL, a view is a virtual table based on the result-set of an SQL statement. It’s essentially a stored query that you can interact with like a regular table. A view contains rows and columns, but it doesn’t store the data itself. Instead, it dynamically generates its data from the underlying base tables each time it’s queried.

Think of a view as a secure and simplified window into your database. It’s a powerful tool for database administrators and developers to:

• Simplify Complex Queries: Hide complex joins, calculations, and filtering logic behind a simple name, making it easier for application developers or data analysts to query the data.
• Enhance Security: Restrict access to specific rows or columns. For example, you can create a view that shows employee names and departments but hides sensitive salary information.
• Provide a Stable API: Create a consistent interface for applications. If you need to refactor the underlying table structure, you can often update the view’s logic to maintain the same output, preventing the need to change application code.

The CREATE VIEW Statement

To create a view, you use the CREATE VIEW statement. Many modern database systems also support CREATE OR REPLACE VIEW, which is incredibly useful during development as it allows you to modify a view’s definition without having to DROP and CREATE it again.

Syntax

CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];

Example: Creating a Product Summary View

Let’s assume we have a Products table with a complex structure. We want to provide a simplified view for our sales team that only shows active products and calculates the final price including tax.

First, here is our base table:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    SupplierID INT,
    BasePrice DECIMAL(10, 2) NOT NULL,
    TaxRate DECIMAL(4, 2) NOT NULL,
    IsActive BOOLEAN DEFAULT TRUE
);

INSERT INTO Products (ProductID, ProductName, SupplierID, BasePrice, TaxRate, IsActive) VALUES
(1, 'Laptop Pro', 101, 1200.00, 0.08, TRUE),
(2, 'Wireless Mouse', 102, 25.00, 0.08, TRUE),
(3, 'Old Keyboard', 101, 15.00, 0.07, FALSE), -- This is an inactive product
(4, '4K Monitor', 103, 450.00, 0.09, TRUE);

Now, let’s create a view to simplify this for the sales team:

CREATE OR REPLACE VIEW V_Active_Product_Pricing AS
SELECT
    ProductID,
    ProductName,
    BasePrice,
    (BasePrice * (1 + TaxRate)) AS FinalPrice
FROM
    Products
WHERE
    IsActive = TRUE;

Querying the View

You can now query this view just like a regular table:

SELECT * FROM V_Active_Product_Pricing WHERE FinalPrice > 100;

The result will only include active products and the calculated FinalPrice:

ProductID	ProductName	BasePrice	FinalPrice
1	Laptop Pro	1200.00	1296.00
4	4K Monitor	450.00	490.50

Enforcing Data Integrity with WITH CHECK OPTION

The WITH CHECK OPTION clause is a powerful feature for updatable views. An updatable view is typically a simple view that maps directly to columns in a single base table. This clause ensures that any INSERT or UPDATE operation performed through the view must conform to the view’s WHERE clause conditions.

In other words, it prevents a user from inserting or updating a row in a way that would make it disappear from the view’s results.

Example

Let’s create a view for employees in a specific department, ‘Sales’, and ensure that any new employees added through this view are also assigned to the ‘Sales’ department.

-- Base table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

-- View with check option
CREATE OR REPLACE VIEW V_Sales_Employees AS
SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department = 'Sales'
WITH CHECK OPTION;

-- This INSERT will succeed because it matches the WHERE clause
INSERT INTO V_Sales_Employees (EmployeeID, Name, Department) VALUES (1, 'Alice', 'Sales'); -- SUCCESS

-- This INSERT will fail because 'Engineering' does not match the WHERE clause
INSERT INTO V_Sales_Employees (EmployeeID, Name, Department) VALUES (2, 'Bob', 'Engineering'); -- ERROR!

The second INSERT statement would be rejected by the database, protecting the logical consistency of the view.

Views vs. Alternatives (CTEs, Materialized Views)

Views are not the only way to structure and reuse SQL. It’s important to know when to use other modern constructs.

• Common Table Expressions (CTEs): Use a CTE (with the WITH clause) for creating a temporary, named result set that exists only for the duration of a single query. CTEs are excellent for improving the readability of complex, multi-step queries without creating a permanent database object like a view.
• Materialized Views: Unlike a standard view, a materialized view stores its result set physically on disk and is refreshed periodically. Use a materialized view for performance-critical reporting on large datasets where the underlying data does not change frequently. This is an advanced feature found in RDBMS like PostgreSQL and Oracle.

Best Practices and Common Pitfalls

• Performance: A view is just a stored query; it doesn’t inherently improve performance. The underlying query runs every time the view is accessed. Ensure the query within the view is well-optimized with proper indexing on base tables.
• Avoid Deep Nesting: Avoid creating views that are based on other views, which are based on other views. This deep nesting can make debugging and performance tuning extremely difficult.
• Naming Conventions: Adopt a clear naming convention to distinguish views from tables, such as prefixing view names with v_ or vw_ (e.g., v_User_Report).
• Dependencies: Be aware that changing or dropping a base table will break all views that depend on it. Modern database tools can help you track these dependencies.