Views are virtual tables that, for the database or the user, look like ordinary tables. But in reality, they are just queries on other tables or views. There are several reasons to use views:
- It can make complex queries simpler by separating subqueries into views, and then make a simpler query on them.
- It makes it possible to set more precise permissions on a table or parts of the table. You can for instance allow a user to use only certain columns of a table.
- It can make huge queries simpler. For example, statistics from many tables can be summarized into a view for easier queries.
You can also create a separate table for complex and resource demanding queries. It can be useful for complex queries that are frequently asked. This is also known as a materialized view or a snapshot. It is not updated automatically, but can be updated from procedures or by the application. A materialized view can also be seen as a cache that stores the result of a query. It therefore needs to be up to date.
In this example, a view is created that shows how many posts each user has written. As previously said, a view is created from a query.
CREATE OR REPLACE VIEW Posts_per_user AS SELECT Users.ID AS ID, Users.username AS username, COUNT(Posts.id) AS count FROM Users LEFT JOIN Posts ON Users.ID = Posts.author GROUP BY Users.ID;
Other articles in this series: