Views vs Materialized Views in RDBMS – which one to use in your SQL code

Introduction

In this article we explore View and Materialized Views concepts in RDBMS and SQL. We will understand each one separately, then highlight the differences as well as the use cases of each.

Note that we will use PostgreSQL as our RDBMS for the tutorial examples, however those concepts are applicable for all major RDBMS like MySQL, Oracle, and Microsoft SQL Server. You just need to check the equivalent syntax and unique underlining concepts of each.

Views

Introduction

View are database objects that store, and run user-defined SQL queries. Meaning that each time we call a View, it runs a certain SQL query without storing any data. You can think of a View as a virtual table in our database that doesn’t store any data. But like a real table, when can retrieve data from it.

Views tutorial example using SQL

In this section we will showcase how Vs work and how we implement them. As mentioned in the introduction, we will use PostgreSQL as our RDBMS.

Let’s say that we have a school database that contain three tables, one for the students, one for the parents, and another for the parents-students mapping as follows:

Students table

Parents table

Students-parents mapping table

let’s say we want to see how many students in our school each parent have. We use the following SQL query for that:

SELECT (p.first_name||' '||p.last_name) parent_name, count(s.id) as student_count
FROM parents p
JOIN student_parent sp ON p.id = sp.parent_id
JOIN students s ON s.id = sp.student_id
GROUP BY (p.first_name||' '||p.last_name)
ORDER BY count(s.id) DESC;

Here’s the resulted data

Now let’s see how to turn that code into a View:

We use the CREATE VIEW name AS command and give it a name

-- creating the view
CREATE VIEW students_count_by_parent
AS
-- actual query
SELECT (p.first_name||' '||p.last_name) parent_name, count(s.id) as student_count
FROM parents p
JOIN student_parent sp ON p.id = sp.parent_id
JOIN students s ON s.id = sp.student_id
GROUP BY (p.first_name||' '||p.last_name)
ORDER BY count(s.id) DESC;

After running the command, we get a success message indicating that our View have been saved as a database object:

Now that our View is saved in our RDBMS, let’s use it to retrieve the data:

SELECT * FROM students_count_by_parent;

Note how SQL treats the View same as a database table.

Here are results:

As you can see, we get the same results as before, but we used one line of code. We didn’t even know what are the names of the tables or what’s the structure of our database or any other information that is regarded as confidential. And that’s the beauty of using View in RDBMS and SQL.

Advantages of Views

Now that we saw what views are, the question is, why do we use them? SQL Views provide two main advantages:

• Security
• Simplification of complex Queries

Security

Security comes in first place due to its importance when it comes to data confidentiality and privacy.

The usual usage of Views’ security would be to create some users on our database, then grant them access to just certain Vs and not the whole database. That way they won’t be able to see anything related to our database structure, tables structure, tables names, relations.. etc. They will simply run one line of code, and get the data. They won’t be able to run custom queries.

Such role are given to external users, business partners, clients… etc. Basically anyone that needs to see some specific SQL retrieved data, and nothing else.

Simplify Complex Queries

As we saw in the example, Views stores any kind of SQL queries, and when called, it runs that code to retrieve the data using the select command, same as we do for tables.

Disadvantages of Views

Vs have one disadvantage compared to MVs:

• No performance regarding the querying speed compared to Materialized Views

No speed performance

This disadvantage only exists because MVs are faster, but compared with usually queries, the execution time of the SQL query is the same.

Materialized Views

Introduction

Materialized Views are database objects that are created over an SQL query same as with Views but differ on Vs in their implementation in the RDBMS.

Materialized View tutorial example

In this section we will walkthrough how to use Materialized Views.

Note that we will use the same tables and SQL query as we did in the View tutorial above (check it out if you didn’t).

To create a materialized view we use the CREATE MATERIALIZED VIEW name AS code, and pass it a name:

-- creating the materialized view
CREATE MATERIALIZED VIEW mv_students_count_by_parent
AS
-- actual query
SELECT (p.first_name||' '||p.last_name) parent_name, count(s.id) as student_count
FROM parents p
JOIN student_parent sp ON p.id = sp.parent_id
JOIN students s ON s.id = sp.student_id
GROUP BY (p.first_name||' '||p.last_name)
ORDER BY count(s.id) DESC;

After running the code, we can see the success message below indicating that our MV was saved as a database object.

Now that our MV is created, we can retrieve the data by referencing name, same as did with Views:

SELECT * FROM mv_students_count_by_parent;

Here’s the resulted table:

We can confirm now that our resulted table is the same as the original query.

At this point, both Vs and MVs look similar, let’s go further to check the differences between them.

Advantages of Materialized Views

The reasons we would use MVs, are due to the advantages we gain:

• Speed of SQL querying
• store the data
• store the query: same as Vs
• Security: same as Vs
• Simplifications of code: same as Vs

Speed

When comparing Materialized Views to standard Views, we notice a big execution time difference. Usually we notice that for queries that take few seconds or even few minutes with Views and standard querying. The same SQL code would take milliseconds with Materialized Views.

Speed of query execution is the main direct reason we use Materialized Views in the first place.

Storing the Query data

Storing the query’s data is what makes the difference in speed between the two Views. Whereas Views run the query each time we call it, Materialized Views run the query at first, then saves the data, and whenever we call it, it retrieves the saved data so it doesn’t run the query each time which makes a huge difference especially in queries that take some time and we need to run them multiple time a day or so.

Save the query

Same as with Views, Materialized Views save the query. The main advantage of that in MVs is when we want to refresh the stored data in the MV since it won’t be updated automatically if the the original data changes.

Security and Simplifications of code

MVs provide the very same benefits as Vs regarding these two points. (check Vs advantages above!)

Disadvantages of Materialized Views

Having all those advantages don’t come that easy, we also have one disadvantages regarding using MVs that is usually what decide wether we use them or not:

• Won’t update the saved query data automatically if the original data changes

Materialized Views Don’t Update the Stored Data Automatically

Since MVs speed relies upon not having to run the query each time, the stored data inside isn’t updated each time, otherwise it will be a standard View.

When we want to update the date of the MV we do that manually using the following command:

REFRESH MATERIALIZED VIEW mv_students_count_by_parent;

However we shouldn’t use it often, otherwise it wont make sens to use a MV in the first place.

Note that different RDBMS provide different functionalities regarding refreshing MVs or anything else regarding Vs and MVs. Check the one you are using on the official their official website for more info.

Should you use Views or Materialized Views in your SQL?

Both Views and Materialized Views share common benefits regarding security and simplification of queries. However have different underlining concepts which makes the difference on which to use for your specific use case.

Views run the predefined query each time we call it whereas Materialized Views run the query the first time, save the retrieved data, then reference that data each time we call the MV.

With that being said, MVs are significantly faster, and their benefits can be noticed when having a large amount of data.

To sum up: if you just want security or simplification of code, use Views. If you want The benefits of the Views, as well as faster querying, for data that don’t need to be updated frequently like monthly reports, use Materialized Views.

Conclusion

This article highlighted the beginners’ friendly explanation and well as examples to both Views and Materialized Views. Note that there are more concepts that you can learn regarding these concepts. But that is not necessary for you to start using them is your projects.

Remember that each RDBMS provides different functionalities and options regarding these concepts.