In the world of databases, a view is a query that’s stored on a database.

The term can also be used to refer to the result set of a stored query.

To create a view, you write a query, then save it as a view.

To run a view, you query it, just like you’d query a table. The difference is that, the view itself is a query. So when you query the view, you’re effectively querying a query.  This enables you to save complex queries as views, then run simple queries against those views.

Views are sometimes referred to as virtual tables, because they present data in the form of a table, but without such a table existing in the database.

A view can present data from multiple tables and present it as though it’s in a single table (just like any other SELECT query).  So when creating a view (just as creating any SELECT query), you specify which columns to display.

Creating a View

Views are created using the (SQL) CREATE VIEW statement. So for example, to create a view called say, “NewCustomers” you would start with:

CREATE VIEW NewCustomers AS

Followed by the query that makes up the view.

Here’s an example of a view that has just been created in SQL Server:

Querying a View

You “run” a view by querying it, just as you’d query a table. So, for example, you could perform a SELECT query against the NewCustomers view like this:

SELECT * FROM NewCustomers

You can also narrow the results down, just as you can with a normal query. So, you could also do this, for example:

SELECT * FROM NewCustomers

WHERE OrderValue > $150

Here’s an example of querying the view that was created in the previous screenshot:

Updatable Views

Some views can be used to update data. So in this case, you can use an INSERT, UPDATE, or DELETE statement to modify the data in the underlying base tables.

However, there are many cases where you can’t update data through a view, for example, if it’s returning aggregate data (such as SUM(), AVG(), etc). It’s best to check the documentation for your DBMS to see what restrictions are placed on updatable views.

Advantages of Views

Views provide several advantages over tables. Here are some of the main benefits of views over tables:

Simplified Code

Views can allow you to write simpler queries. So, rather than running a complex query against a table, you can run a simple query against a view (which contains the complex query).

Enhanced Security

Views can restrict access to only the data that a given user is authorised to access.

For example, you could give a user access to a view, but not the underlying table/s that the view queries. Therefore, the user will only have access to the columns defined in the view.By excluding certain columns in the view, you are denying the user access to the data in those columns.

Better Usability

Views can present data in a simplified way. So data can be presented in a way that makes more sense to the user (rather than imposing the underlying table structure on them).

Better Maintainability

Views can also help in cases where the table structure is changed.

If a database’s table structure is changed, a view can make this seamless to the user. Users can continue to query the view without knowing the underlying tables have been changed. In this case, the view could be updated to query the new table structure, while returning the same “virtual table” to the user, along with the same column names.