Views in SQL – Complete Guide with Examples

What is a View in SQL?

A view in SQL is a virtual table created from the result of a SELECT query. It doesn’t store data itself but displays data from one or more real tables.

In Simple Words:

A view is like a saved custom query that you can use like a table. 

Key Features of views:

• It looks and behaves like a table, but doesn't store data.

• Always shows up-to-date data from the underlying tables.

• Can include filters, joins, and column renaming.

• Can be used to simplify complex queries or hide sensitive data.

Example

Suppose we have a table:

Now let’s see how to create a view.

We can create a view to show only high-salary employees:

Now, using this view:

Why Use Views:

• To simplify complex SQL queries.

• To restrict access to specific rows or columns for security.

• To provide a consistent interface even when the underlying table changes.

Are Views Read-Only?

• Most views are read-only by default.

• However, certain views (called updatable views) allow INSERT, UPDATE, or DELETE operations if they meet specific conditions such as being based on a single table without aggregates or joins.

Let’s talk about type of views.

Types of Views

1.      Simple View

2.      Complex View

3.      Materialized View

4.      Updatable View

5.      Read-Only View

Let’s understand each one in detail.

1.   Simple View

A simple view is based on a single table, without any joins, GROUP BY, or aggregate functions.

It is the most basic form of a view — like a filtered window into a table.

Key Features of Simple Views:

• Based on one table only.

• Can select specific columns or use a WHERE clause

• Often updatable (you can insert, update, or delete through the view if allowed) …(we will see later)

• Doesn’t include functions, joins, or grouping.

Example:

Suppose you have a table:

Now, let’s create a simple view on this table.

We can create a simple view to show only employees from the "Sales" department:

Now, we can use this view like a table:

Why Use Simple Views?

• To restrict access (e.g., hide some columns).

• To make queries easier and cleaner.

• To reuse commonly used filters.

2.   Complex View

A complex view is a virtual table created using a SQL query that combines data from multiple tables and may include joins, aggregate functions, grouping, or subqueries.

It is typically used for reporting and analysis and generally not updatable .

In Simple Words:

A complex view combines data from different tables and often processes it — like calculating totals, averages, or grouping.

Example:

Suppose you have the following tables:

The Employees table stores employee details including which department they belong to and their salary. The Departments table stores department names, and the Projects table tracks projects under each department.

Now, you want to create a summary view per department that shows:

• Department name

• Total number of employees

• Average employee salary

• Total number of projects

• Number of employees earning more than 60,000

To achieve this, you need to combine all three tables using joins, aggregation, and a subquery.

Example SQL Query (for Complex View):

Querying the view

Once the complex view is created, you can simply run:

This will return the summarized report for each department without writing complex joins and aggregations logic again.

Why use Complex Views?

• Simplify complex queries: Write the query once; use it multiple times without repeating the logic.

• Enhances Reusability: Instead of rewriting complicated queries multiple times, you define the logic once in a view.

• Data abstraction and security: You can hide sensitive fields and only expose relevant information to users or applications.

3.   Materialized View

A Materialized View is a database object that stores the result of a query physically as a table. Unlike a regular view, which runs the query every time you request data, a materialized view keeps a copy of the data. This makes reading data much faster because the system does not need to re-run the complex query each time.

Unlike regular (virtual) views, materialized views require periodic refresh to update the stored data when underlying tables change.

Why use Materialized Views?

• When your query involves large tables or complex joins and calculations, running it repeatedly can be slow.

• A materialized view helps by saving the results of that query.

• This way, you can quickly get the results without waiting for the database to calculate everything again.

Though MySQL does not support materialized views natively, they are supported by databases like Oracle, PostgreSQL, Microsoft SQL Server, and MariaDB.

Example: Materialized View in PostgreSQL

Suppose you have a Sales table:

Create a Materialized View

This materialized view stores the total sales per region:

Query the Materialized View

This query reads the precomputed results stored in the materialized view, so it’s much faster than running the aggregation on Sales every time.

Refresh the Materialized View

Since changes in the Sales table do not automatically reflect in the materialized view, it must be refreshed manually.

This will update the stored data with the latest sales information.

4.   Updatable View

An updatable view allows modifications like INSERT, UPDATE, and DELETE operations to be performed directly on the view, which in turn modifies the underlying tables (the tables from which the view is created). Essentially, it provides a simplified interface to interact with and modify data without directly accessing the original table.

When is a view updatable?

The key rule is: 

The database must be certain which specific piece of data you're trying to change when you interact with the view. 

If there's any confusion or uncertainty, the database system won't let you update the view, as it can't guarantee it's modifying the correct data.

Simple Analogy:

• Updatable View: Like looking through a clear glass window at a document. If you point to a word and say "change this," the database system can trace your finger to the exact word on the document and change it.

• Non-Updatable View: Like looking at a summarized report or a photo of a document. If you point to something and say "change this," the database can't tell which original document, or even which specific part of an original document, you mean. It might be a calculated value, or data from multiple sources. So, it won't let you change it.

Conditions for a View to Be Updatable:

1. Based on a Single Table:  The view must be derived directly from only one table. If it includes data from multiple tables, the database won't know which table to update if you try to change something.

2. Include the Primary Key: The view must include the unique identifier (the primary key) of the original table so that the database can uniquely identify rows.

3. No Aggregate Functions: The view can't use functions like SUM(), AVG(), COUNT(), MIN(), MAX(). These functions create new, calculated values. You can't update a sum because it's the result of many numbers. Changing the sum doesn't tell the database which number to change.

4. No DISTINCT Keyword: The view shouldn't be defined using SELECT DISTINCT. If DISTINCT removes duplicate rows, and then you try to update one of those "unique" rows in the view, the database won't know which of the original duplicate rows to update.

5. No GROUP BY or HAVING Clauses: The view shouldn't group rows or filter those groups. Similar to aggregate functions, GROUP BY creates summarized groups of data. If you try to update a "group," which individual row(s) should be modified? The database can't tell.

6. Simple Column References only: The columns in your view should directly match columns from the base table, without any math or text manipulation (like column + 3 or UPPER(column)). If a column in your view is Price * Quantity (a calculated value), how would the database update the original Price or Quantity when you try to change the calculated result? It needs a direct link.

7. Excluded Columns Must Have Defaults or Be Nullable: If the view leaves out any columns from the original table:

   • That columns must have a value (i.e., they are NOT NULL), or those missing columns must have a default value set in the original table.

   • If you try to add a new row using the view, and the view doesn't ask for a value for a NOT NULL column, the database still needs a value for it. If a default value is defined, the database can automatically fill it in.

   
   

Example:

To demonstrate the conditions under which a view is updatable, let's look at several examples. First, consider the creation of a table called Products that will be used to demonstrate updatable views:

Updatable view

This view is based on a single table, includes the primary key (ProductID), and uses simple column references without aggregates, DISTINCT, or GROUP BY. As a result, it is updatable.

Example DML operation (Update):

This will successfully update the price of the product with Product ID 1 in the underlying Products table through Electronics Products view.

5.   Read-Only View

In the context of database management, a read-only view is a virtual table that allows you to retrieve (read) data but does not allow you to modify the underlying data through the view itself. This means you cannot perform INSERT, UPDATE, or DELETE operations directly on a read-only view. If you attempt to do so, the database system will return an error.

Why are views read-only?

A View become read-only when its definition makes it ambiguous or impossible for the database to trace any changes back to the original source table. This usually happens when the view involves certain complex operations or transformations that break the one-to-one mapping between the view and the base data.

Common Reasons Why a View is Read-Only

• Aggregations or Grouping: Using functions like SUM(), AVG(), COUNT(), MIN(), MAX(), or clauses like GROUP BY and HAVING generates summarized or grouped data that can't be easily translated into changes in individual rows.

• Calculated Columns: Columns with expressions like Price * Quantity are derived values. The database doesn’t know how to update the original Price or Quantity fields if you try to edit the result.

• Multiple Tables with Joins: Views combining multiple tables with joins makes it unclear which table to modify when an update is attempted.

• DISTINCT Clause: If DISTINCT is used to remove duplicates, the database can't tell which original row to modify.

• Set Operators: Using UNION, INTERSECT, or EXCEPT combines data from different sources, making updates ambiguous.

• Missing NOT NULL Columns: If the view doesn't include a NOT NULL column from the base table and that column doesn't have a default value, the database cannot insert new rows through the view.

Example of a Read-Only View:

Using a Products table, a read-only view can be created to summarize product information:

• This view summarizes data using aggregate functions and GROUP BY.

• Since it doesn’t reference individual rows directly, it is read-only.

• Any attempt to UPDATE or INSERT through this view will result in an error.

Operations on SQL views

SQL Views are virtual tablescreated from a SELECT query. They don't store data themselves but instead provide a dynamic window into the underlying base tables. You can perform various operations on views, similar to real tables, but with certain limitations, especially when it comes to modifying data. 

Let’s explore the most common operations on views using a sample Employees table:

1.   CREATE VIEW: Defining a view

Use CREATE VIEW to define a new view. This view will specify which columns and rows from the base table(s) should be visible through the view. 

Example: Create a view to show only HR employees, excluding their salaries for security reasons.

Now, the HR_Employees view exists, but it doesn't store any data itself. It simply defines how to retrieve data from the Employees table when it's queried.

2.   SELECT from View: Retrieving data

You can query a view using the SELECT statement just like a regular table. 

Example: Retrieve all data from the HR_Employees view.

3.   INSERT into View: Adding data

If the view is updatable, you can insert data through it — and it will be stored in the underlying table. Make sure the view satisfies all the conditions of an updatable view (as discussed earlier). 

Example: Insert a new HR employee through the HR_Employees view.

After this, querying the Employees table will show Eve Adams, with her Department correctly set to 'HR' and her Salary as NULL (assuming it's nullable in the base table, as we didn't specify a value and it wasn't a NOT NULL column without a default value).

4.   UPDATE View: Modifying data

You can modify existing rows in the base table(s) via an updatable view. For updating a view it must follow all the conditions of an updatable view.

Example: Update Alice Smith's last name through the HR_Employees view.

Now, if you query the Employees table, Employee with EmployeeID = 1 will have last name as 'Williams'.

5.   DELETE from View: Removing data

You can delete rows from the base table(s) through an updatable view. 

Row with EmployeeID = 3, will be removed from the Employees table.

6. ALTER VIEW: Changing the view's definition

Use ALTER VIEW to change an existing view's definition. This allows you to change the columns or the filtering criteria without having to drop and recreate the view and potentially lose associated permissions. When you modify an existing SQL view using ALTER VIEW, you're essentially telling the database: "Keep the view with this name, but change how it's defined (e.g., which columns it shows or which rows it filters)."

The crucial benefit of ALTER VIEW is that it preserves the permissions that have already been granted on that view. The view's definition is updated, but because the view object itself was not destroyed and recreated, all existing permissions remain intact.

Example: Add the Salary column to the HR_Employees view.

Now, querying HR_Employees will also return the salary for HR employees.

7. DROP VIEW: Deleting the view

This operation permanently removes a view from the database schema. It only removes the view definition, does not affect the base table or its data.

Example: Drop the HR_Employees view.

After this, the HR_Employees view will no longer exist. Attempts to select from it will result in an error indicating the view does not exist. The underlying Employees table and its data remain untouched.

The WITH CHECK OPTION Clause – Enforcing Filter Conditions

When you create an updatable view, you can add the WITH CHECK OPTION clause. This ensures that any INSERT or UPDATE operation performed through the view does not create rows that violate the view's filtering conditions (the WHERE clause).

Example:

Now if you run:

The system will throw an error because this update would move the employee out of the 'IT' department, which violates the view's condition.

Want to learn more about SQL?

Join our Data Analytics Course now to begin learning the skills necessary for a prosperous career and to expand your knowledge of SQL, databases, and data analysis!