SQL Views: A Comprehensive Guide

In SQL, a view is a virtual table that consists of a result set generated from a query on one or more base tables. Views act as a simplified or customized representation of data, making it easier to work with complex queries or sensitive data without altering the original table structure.

Views do not store the actual data but display data dynamically from the underlying tables whenever queried. They provide an additional layer of security and convenience for managing large and complex databases.

Creating a View

To create a view, the CREATE VIEW statement is typically used. This statement defines the view by writing a SQL query that specifies which data the view will dynamically display

Syntax (Same in MySQL, MSSQL, and PostgreSQL):

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

Example:

CREATE VIEW employee_view AS
SELECT id, name, salary
FROM employees
WHERE department = 'HR';

In this example, employee_view will display only the id, name, and salary of employees in the HR department.

Types of Views in SQL

SQL supports various types of views, depending on their functionality and how they manage data.

1. Simple View

A simple view is based on a single table and does not involve any complex SQL functions like JOIN, GROUP BY, or DISTINCT. It allows performing SELECT, INSERT, UPDATE, and DELETE operations, provided the operations do not violate integrity rules.

Syntax:

CREATE VIEW simple_view AS
SELECT id, name
FROM employees;

Example: A view that displays only the id and name columns from the employees table.

2. Complex View

A complex view is based on multiple tables and can include SQL functions such as JOIN, GROUP BY, DISTINCT, or aggregate functions like SUM, COUNT, etc. These views are read-only, meaning you cannot perform INSERT, UPDATE, or DELETE operations directly on them.

Syntax:

CREATE VIEW complex_view AS
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Example: A view that displays the no.of employees in each department using GROUP BY clause.

3. Materialized View

A materialized view is a view that stores the result of the query physically in the database. Unlike regular views, which are virtual and do not store data, materialized views physically store the result set and are refreshed periodically to reflect changes in the underlying tables. This makes querying faster, but the data might not always be up-to-date.

Syntax (Supported by MSSQL and PostgreSQL, not natively supported in MySQL):

PostgreSQL:

CREATE MATERIALIZED VIEW materialized_employee_view AS
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

MSSQL (uses indexed views):

CREATE VIEW materialized_employee_view WITH SCHEMABINDING AS
SELECT department, SUM(salary) AS total_salary
FROM dbo.employees
GROUP BY department;

CREATE UNIQUE CLUSTERED INDEX idx_view ON
materialized_employee_view (department);

Example: A view that stores and displays the total salary per department. Since the view stores the results physically, it allows faster access to this aggregated data.

4. Updatable View

An updatable view allows INSERT, UPDATE, and DELETE operations on the view, which in turn reflects on the underlying base table(s). However, certain conditions must be met, such as the view should be based on a single table, and the view should not contain complex SQL functions like GROUP BY, DISTINCT, or JOIN.

Syntax:

CREATE VIEW updatable_view AS
SELECT id, name, salary
FROM employees
WHERE department = 'IT';

Example: You can update employee salaries directly through the updatable_view, and the changes will reflect in the employees table.

Dropping a View

To remove a view from the database, the DROP VIEW statement is used.

Syntax (Same in MySQL, MSSQL, and PostgreSQL):

DROP VIEW view_name;

Example:

 DROP VIEW employee_view;

Conclusion

Views in SQL offer a powerful way to simplify complex queries, enhance security by limiting access to sensitive data, and create customized representations of data without modifying the underlying tables. Depending on your needs, you can use simple views for basic data display, complex views for aggregations, or materialized views for faster querying. It’s essential to understand the type of view that best suits your requirements to efficiently manage your data.

Knowledge Check