SQL Views: Complete Guide with Syntax, Types, Examples & Best Practices

What are SQL Views?

SQL Views are one of the most useful database objects in SQL. A view is a virtual table created from one or more SQL queries. Instead of storing data physically, it displays data from the underlying tables whenever it is queried.

Views simplify complex SQL statements, improve database security, and help developers reuse queries without rewriting them repeatedly.

Whether you use MySQL, SQL Server, PostgreSQL, or Oracle, SQL Views make database management cleaner and more efficient. They are widely used in reporting, dashboards, business intelligence, and enterprise applications..

Creating a View

After syntax, explain each part.

Explanation

The view automatically reflects changes made to the underlying tables when queried (regular views)

CREATE VIEW creates a new view.

view_name specifies the name of the view.

SELECT defines the query stored inside the view.

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

SQL Views are an essential feature for simplifying database queries, improving security, and promoting code reuse. Whether you need to present filtered data, hide sensitive information, or simplify complex joins, SQL Views provide a flexible and maintainable solution. Understanding simple views, complex views, materialized views, and updatable views will help you design cleaner, more efficient database applications and prepare for SQL interviews.

Knowledge Check

Frequently Asked Questions