Oct 155 min read

A Comprehensive Guide to SQL - Views

Welcome back to the SQL series! We now dive into a critical feature in SQL databases—Views. Views offer an abstraction over database tables that simplifies querying, improves security, and enhances maintainability. Whether you're working with simple queries or complex joins, views can make your life easier.

This blog covers SQL Views from basic concepts to advanced scenarios, including an explanation of INSTEAD OF triggers used to update views effectively. Additionally, we'll explore concepts related to temporary views and how PostgreSQL handles them.

What is a SQL View?

A view in SQL is a virtual table that is created based on a query result. Unlike physical tables, views do not store data themselves but fetch it from underlying tables whenever accessed. They allow users to simplify complex queries, manage security by restricting access to certain data, and provide an interface that abstracts away the complexities of the underlying table structure.

Key Characteristics of Views:

Virtual Table: Views exist as saved SQL queries that reference other tables. They don’t store data but present a "window" into it.
Read-Only or Updatable: Views can either be read-only or updatable depending on their structure.
Data Security: Views help restrict access to certain columns or rows by providing a filtered or masked version of the underlying data.
Simplification: By encapsulating complex SQL logic, views make queries easier to write and understand.

Why Use SQL Views?

Views offer many benefits:

Simplified Queries: Complex SQL logic, such as joins, aggregations, and filters, can be encapsulated within a view, reducing the need to rewrite queries multiple times.
Security: Views can expose specific columns or rows from a table, restricting access to sensitive data.
Logical Abstraction: You can present the data in a more user-friendly manner while hiding the complexities of the underlying database schema.
Data Consistency: Views help maintain consistent querying standards across an organization.

Basic Syntax for Creating a View

Here’s the basic syntax to create a view in SQL:

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

This creates a virtual table based on the result of a SELECT query. Once a view is created, querying it is as simple as querying a normal table:

SELECT * FROM view_name;

Example 1: Creating a Simple View

Let’s say you want to create a view that displays only the employees and their respective departments:

CREATE VIEW EmployeeDepartmentView_V
AS
SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

In this example, we’ve created a view EmployeeDepartmentView that joins two tables, Employees and Departments, to present the employee and department information in a simplified form.

Example 2: View with Multiple Tables

You can include multiple tables in your view’s SELECT statement, just as you would in a normal SQL query. Views are not limited to a single table—they can be constructed using complex joins and conditions across multiple tables.

CREATE VIEW OrderCustomerView AS 
SELECT o.OrderID, o.OrderDate, c.CustomerName, p.ProductName 
FROM Orders o JOIN Customers c 
ON o.CustomerID = c.CustomerID 
JOIN Products p ON o.ProductID = p.ProductID;

This view joins three tables—Orders, Customers, and Products—to provide a comprehensive view of order details, customer information, and product names in one simple query.

Temporary vs Permanent Views

In PostgreSQL, you can create temporary views using the TEMP or TEMPORARY keyword. These views exist only for the duration of your session and are automatically dropped once the session ends.

Example: Creating a Temporary View

CREATE TEMP VIEW TempEmployeeView AS
SELECT EmployeeID, EmployeeName
FROM Employees_V
WHERE DepartmentID = 2;

This temporary view will only exist during your current session. Once the session ends, the view will be dropped automatically.

If you do not use the TEMP keyword, the view will be created in the permanent space and will persist until explicitly dropped.

DROP VIEW IF EXISTS TempEmployeeView;

This statement will delete the view permanently from the database, making it unavailable for future queries.

Updatable Views and INSTEAD OF Triggers

When you have views based on multiple tables or complex logic (such as joins or aggregates), performing an INSERT, DELETE, or UPDATE operation on the view does not automatically modify the underlying tables. However, with an INSTEAD OF trigger in place, these modifications to the view ensure that the changes are applied to the underlying tables as well.

Example 3: Updating a View Using INSTEAD OF Trigger

Now, we are creating two tables: Employees and Departments. We then combine these tables using a View to display employee and department data. An INSTEAD OF trigger is applied to the View to manage updates to employee department details. This trigger ensures data integrity by updating the underlying tables when altering the View.


-- Step 1: Create Employees Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    DepartmentID INT
);

-- Step 2: Create Departments Table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);


-- Step 3: Insert Data into Employees Table
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES (1, 'John Doe', 1),
       (2, 'Jane Smith', 2);


-- Step 4: Insert Data into Departments Table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'),
       (2, 'Finance');


-- Step 5: Create View to Combine Employee and Department Data
CREATE VIEW EmployeeDepartmentView
AS
SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;


-- Step 6: Query the View to See Combined Data
SELECT * FROM EmployeeDepartmentView;


-- Step 7: Create or Replace Rule for Updating the View
CREATE OR REPLACE RULE UpdateEmployeeDepartment AS
ON UPDATE TO EmployeeDepartmentView
DO INSTEAD
    UPDATE Employees
    SET EmployeeName = NEW.EmployeeName,
        DepartmentID = (SELECT DepartmentID
                        FROM Departments
                        WHERE DepartmentName = NEW.DepartmentName)
    WHERE Employees.EmployeeID = NEW.EmployeeID;


-- Step 8: Update Department Information Through the View
UPDATE EmployeeDepartmentView
SET DepartmentName = 'Finance'
WHERE EmployeeID = 1;


-- Step 9: Verify the Update in the Employees Table
SELECT * FROM Employees_V;

Advanced Concepts in SQL Views

1. Indexed Views (Materialized Views)

An indexed view or materialized view stores the result of the query physically, which can drastically improve performance for frequently accessed data.

-- Create a unique index on EmployeeID
CREATE UNIQUE INDEX EmployeeID_Index
ON Employees (EmployeeID);


-- Cluster the Employees table based on the EmployeeID_Index
CLUSTER Employees USING EmployeeID_Index;


SELECT * FROM Employees;

Indexed views come with the overhead of maintaining the index, so they are only recommended when performance gains outweigh the costs of maintaining the physical data.

2. Partitioned Views

These are SQL views that combine data from multiple tables, allowing you to query them as if they were a single table. This approach helps manage large datasets by breaking them into smaller, more manageable parts.

Suppose you have two tables for employees, one for those hired in the first quarter (Employees_Q1) and another for those hired in the second quarter (Employees_Q2). Instead of querying each table separately, you can create a partitioned view to see all employees at once:

CREATE VIEW AllEmployees 
AS
SELECT  FROM Employees_Q1 
UNION ALL  
SELECT  FROM Employees_Q2;

In this example, the AllEmployees view combines data from both Employees_Q1 and Employees_Q2, so when you query AllEmployees, you get a complete list of all employees hired in the first and second quarters without needing to look at each table individually.

Security and Permissions in Views

Views can play a critical role in enhancing database security. You can expose only certain columns or rows of data via a view and prevent users from accessing sensitive information in the underlying tables.

For example, you could create a view that shows only non-sensitive employee information, like this:

CREATE VIEW PublicEmployeeView AS 
SELECT EmployeeID, EmployeeName FROM Employees;

Users querying this view won’t have access to sensitive columns like Salary or SocialSecurityNumber.

SQL Views provide a robust and flexible mechanism for querying data in SQL databases, allowing you to encapsulate complex queries, improve security, and abstract away database details. Whether you're using views to simplify queries or employing INSTEAD OF triggers to manage updates across multiple tables, views can play a vital role in your database design.

We also discussed temporary and permanent views in PostgreSQL, showcasing how you can manage session-based or persistent views. Understanding views can simplify your work, make your queries more efficient, and enhance the maintainability of your database systems.

Happy Views Ahead!