1. Overview
When we use SQL databases, we might want to create a view to represent data from one or more tables. We have different types of views. However, the main difference is if a view only works as a restriction and simplifies a query or improves the access performance to some data we regularly fetch. That is why we talk about generic SQL views (simple or complex) or, for example, materialized views that store the view data as a cache.
In this tutorial, we’ll talk about simple and complex SQL views and how they differ from materialized views with some practical examples.
2. Why Do We Need Views
A view is an SQL statement stored in the database with a name. It is like a virtual table that displays a subset of data from one or more tables.
Suppose we have a query we often reuse or expose to a web application. With a view, we give a name to this query and store it in the database.
A view makes a statement more readable, for instance, if a query joins tables from one or more databases. To improve security, we can grant, for example, views permissions to hide sensitive information for some users.
Moreover, we’ll also see how we can think of scaling a query when using a materialized view.
Before moving forward, let’s consider this subquery syntax we often use:
SELECT * FROMm
(
SELECT
column1,
column2
FROM
some_table
) s;
Notably, we can call this subquery an Inline view. All the views work with the same concepts but also use a DDL command to store this query as a reusable statement.
3. Database Example
First, let’s create an Employee-Department database test using, for example, PostgresSql. We want a department table with an id and a name:
CREATE TABLE department (
department_id int8 NOT NULL,
department_name varchar(255) NOT NULL,
CONSTRAINT department_id_pkey PRIMARY KEY (department_id)
);
Let’s also define an employee table with a foreign key to the department:
CREATE TABLE employee (
employee_id int8 NOT NULL,
employee_name varchar(255) NOT NULL,
department_id int8 NOT NULL,
salary int8 NOT NULL,
CONSTRAINT empolyee_id_pkey PRIMARY KEY (empolyee_id),
CONSTRAINT fk_department_department_id FOREIGN KEY (department_id) REFERENCES department(department_id)
);
4. Simple and Complex Views
If a query is not resource intensive and the result often changes, we can look at simple or complex views.
4.1. Simple View
A simple view contains only one base table or fetches data from only one table.
Suppose we want to show employees’ info through a web application or to a database user. We can create a view that hides all the salary details. For example, we’ll show only the id and name. Therefore, for non-admin users, we can grant access to this view and hide the employee table.
Let’s create a simple view of the employee table for regular users:
CREATE VIEW employee_details AS
SELECT
employee_id,
employee_name
FROM employee;
Once the view is created, we can SELECT from it as it is a table:
SELECT employee_id, employee_name
FROM employee_details;
We can see an output example of the view:
4.2. Complex View
A complex view contains more than one base table. Furthermore, it can have Group by, Distinct, columns defined by expressions, and aggregate functions.
Let’s start with a view of the employees joining the department table:
CREATE VIEW employee_department AS
SELECT
e.employee_id,
e.employee_name,
d.department_name
FROM employee e
INNER JOIN department d ON e.department_id = d.department_id;
Likewise, we can read from it:
SELECT employee_name, department_name
FROM employee_department;
We can check out the result set showing the data with the employee and their department:
Furthermore, let’s make a view with a more complex query. For example, let’s create a view of the salary for every department using aggregation:
CREATE VIEW department_salary AS
SELECT
d.department_id,
d.department_name,
COUNT(e.*) AS employee_count,
SUM(e.salary) AS total_salary
FROM department d
INNER JOIN employee e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
Again, let’s query this view to get the result set:
As we can consider a view like a table, we can apply a where condition to it:
SELECT department_id,
department_name,
employee_count,
total_salary
FROM department_salary
WHERE department_id = 2;
4.3. DML on Views
Although not recommended, we can perform DML operations such as INSERT, DELETE, or UPDATE through a view.
For example, we can update the name of an employee:
UPDATE employee_details
SET name = 'Eric Johnson'
WHERE employee_id = 6;
DML statements are possible on a simple view that contains a single table. We can’t apply DML on a complex view directly.
5. Materialized View
A materialized view stores the results of the query. We might have, for example, expensive statements with subqueries joins, and aggregations. Saving the result set can drastically improve performance. Therefore, we often use a materialized view for large data sets we need to access in constant time.
Unfortunately, not all SQL databases have the same usage for a materialized view.
For example, MySQL has not an in-built system for that. So, we might need to use a plug-in such as LeapDB.
With PostgreSQL, we have a DDL command for a materialized view. However, we need a manual refresh if we need the latest data.
Differently, the Oracle database, being more oriented to DataWarehouse, has an advanced DDL definition, with features such as, for example, refresh on schedule or commit. This is done using materialized view logs that record all changes to the data and can be refreshed incrementally.
Let’s see how we can create a materialized view with PostgreSQL for the employee’s salary grouped by department query we have seen earlier:
CREATE MATERIALIZED VIEW materialized_department_salary AS
SELECT d.department_id,
d.department_name,
COUNT(e.*) AS employee_count,
SUM(e.salary) AS total_salary
FROM department d INNER JOIN employee e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
For example, we can query using, for example, a where condition:
SELECT *
FROM materialized_department_salary
WHERE department_id = 5;
We can then have a look at the result set:
Finally, because a view is like a database table, we might as well apply indexes or partitions to it to improve the query performance.
For instance, we can add an index to our salary department view:
CREATE UNIQUE INDEX ON materliazed_department_salary (
department_id,
employee_count,
total_salary
);
6. How Simple and Complex Views Differ From Materialized Views
With a regular view (simple or complex), we can simplify a query statement or restrict access to database data by granting its usage to some specific users or tenants of our database. However, each time we access it, it will run the relative query.
A materialized view instead stores data on the disk. It serves as a snapshot view of the data. Although it is static data, we can refresh it if required. Materialized views can improve query performance. Thus, the data is pre-computed and stored in a table for faster access.
We can choose a materialized view when performing and accessing data quickly is relevant. However, the data we access might not be refreshed, so we’ll probably not see the latest version of it.
Although views nowadays are still in use, they are getting deprecated due to the rise of NoSQL databases such as key-value storage which offers very low latency to data access.
Nonetheless, we can still see a materialized view in use, for example, in SQL analytics database or offline data access.
7. Conclusion
In this tutorial, we saw how regular views (simple or complex) differ from materialized views. Simple or complex views run the query each time we access them.
Materialized views store data on the disk. The data can occasionally update depending on the view definition. We can apply index or partitions to a materialized view or create it from tables already indexed or partitioned.
Simple or complex views are mainly used for security. Materialized views are still commonly adopted in DataWarehouse or databases for analytics.