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:
Some views can be used to update data. So in this case, you can use an
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
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:
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).
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.
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).
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.