SQL Views and Materialized Views: The Complete Guide

By the end of this article, you’ll know all about views, materialized views, how to create them, their advantages, and much more.

What is a View?

A view is an SQL statement that’s stored in the database. This statement, or view, has a name.

A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table.

If you look at a SELECT query that uses a view, sometimes you might not know that you are querying from a view. The name of the object being queried from could represent a table or a view (or a synonym!).

One thing to remember is that the view object is only a stored SQL query. This means that no data is stored along with the view. The view object only stores your SQL query, and whenever you query the view, it runs the query that is stored against it.

Why Use a View?

So if a view is just an SQL statement with a name, why would you use one?

There are several reasons to use views in SQL. This applies to any SQL variation you use (Oracle, SQL Server, MySQL, etc).

Simplify Queries

The main advantage to using views is that they simplify your queries.

As we learned above, views are just stored SQL statements. These statements can include, for example:

• Selecting of different columns
• Joins to other tables
• Functions, including aggregate functions
• WHERE clauses
• GROUP BY and HAVING

So, if you have a complicated query or logic that’s used in many places, you can use a view to hold that logic. You can then query from that view whenever you need that data.

For example, you can create a view that counts the number of orders and totals the order volume for all orders for each month. You can then query this view and limit by month to find the data you need for a specific month.

We’ll look at how to create a view and see some examples later in this article.

Security

Another benefit of using views is increased security. You can create a view that only selects certain columns from a table. Then, certain users, or applications, can be given access to this view rather than the entire table.

For example, you may have an employee table with salary information. You might not want all applications or users to see this salary information.

So, you can create a view that selects all columns except for the salary columns, and then give access to that view to other users (and don’t give them access to the employee table).

This means when they query the employee view, they won’t see the salary information.

How to Create a View in SQL

To create a view in SQL, follow this syntax:

1CREATE [OR REPLACE] VIEW viewname AS
2SELECT select_query;

This is specifically for Oracle SQL but it should be similar for all other databases.

It includes several components:

• CREATE VIEW: specifiess that we are creating a view.
• OR REPLACE: this optional query lets you replace a view that already exists with the same name, which means you don’t have to drop it first.
• viewname: the name of the view to create. This will be used in queries in the future to refer to this view.
• AS SELECT: specifies that the view translates to the following SELECT query.
• select_query: the SELECT query to be stored and used for the view. Selecting from the view will run this query.

Let’s take a look at some examples.

Examples of Creating a View

Sample Data

Our views are going to be based off the following example data:

Employee:

Department:

Create a Simple View

Let’s create a simple view to demonstrate the concept of a view.

We’ll create a view that shows the emp_id and last_name columns from the employee table.

1CREATE VIEW emp_details AS
2SELECT
3emp_id,
4last_name
5FROM employee;

Once this view is created, we can SELECT from it as though it is a table.

1SELECT emp_id, last_name
2FROM emp_details;

Result:

It acts just like a table.

Create a View for Security

In this example, we’ll create a view on the employee table that shows all employee data except for the salary column. This way, only certain users can access the full table, and everyone else can access the view.

1CREATE VIEW employee_rst AS
2SELECT
3emp_id,
4last_name,
5dept_id
6FROM employee;

The view is called employee_rst, and the “rst” is meant to stand for restricted. The name is up to you.

Now, whenever anyone queries the employee_rst view, they will see this data:

1SELECT emp_id,
2last_name,
3dept_id
4FROM employee_rst;

Only those users with access to the full employee table will be able to query the full table. I’ll have a separate post on privileges to explain how to do that.

View with Joins

Let’s look at an example that joins two tables together. This view is a good example of simplifying the query.

We’ll create a view that shows all employee and department information in the one view.

1CREATE VIEW empdept AS
2SELECT
3e.emp_id,
4e.last_name,
5e.salary,
6d.dept_id,
7d.dept_name
8FROM employee e
9INNER JOIN department d ON e.dept_id = d.dept_id;

This creates a view based on that query that performs the join.

Now we can query that view.

1SELECT emp_id,
2last_name,
3salary,
4dept_id,
5dept_name
6FROM empdept;

Result:

We don’t need to specify any table aliases here, because all columns are in the view, and that is handled when we created the view. We just select the columns as though they are all in the same table.

View with Aggregate Functions

Now let’s take a look at a view that uses aggregate functions. Let’s say we wanted to find the department ID and name, the number of employees, and the total salary in each department.

Our SELECT query would look like this:

1SELECT
2d.dept_id,
3d.dept_name,
4COUNT(e.*) AS emp_count,
5SUM(e.salary) AS total_salary
6FROM department d
7INNER JOIN employee e ON d.dept_id = e.dept_id
8GROUP BY d.dept_id, d.dept_name;

Result:

This shows the result we want.

Now let’s turn this into a view.

1CREATE VIEW dept_stats AS
2SELECT
3d.dept_id,
4d.dept_name,
5COUNT(e.*) AS emp_count,
6SUM(e.salary) AS total_salary
7FROM department d
8INNER JOIN employee e ON d.dept_id = e.dept_id
9GROUP BY d.dept_id, d.dept_name;

Because the view is a stored SQL statement and not a separate table, the query in the view is run each time the view itself is queried, so the numbers are always up to date.

Now, to find this data, all we need to do is query the dept_stats view.

1SELECT dept_id,
2dept_name,
3emp_count,
4total_salary
5FROM dept_stats;

Result:

We can also filter the view using the WHERE clause, if we only wanted to see data for a particular department.

1SELECT dept_id,
2dept_name,
3emp_count,
4total_salary
5FROM dept_stats
6WHERE dept_id = 2;

Result:

So, as you can see, views can be quite easy to create and useful to have in your database.

Inserting or Updating Data in a View

In this section, we'll look at inserting and updating data in a view.

Can You INSERT or UPDATE Data In a View?

We’ve shown some examples on how to SELECT data from a view as though it was a table.

What if you want to update or insert data in a view, as though it was a table?

You can do that, in certain circumstances. It depends on the query that is used to create the view.

You can insert or update data in a view if the view does not:

• Have any DISTINCT keywords, aggregate functions, or window functions in the SELECT clause
• Use set operators (e.g. UNION)
• Use subqueries in the SELECT clause or marked as read-only
• Use the GROUP BY, HAVING, ORDER BY, MODEL, CONNECT BY, or START WITH clause
• Contain pseudocolumns or expressions

The view must also include all NOT NULL columns for an INSERT to be possible.

When updating or inserting, the data being inserted or updated must be targeted at a key-preserved table. A key-preserved table is where every primary key and unique key in the underlying table exists in the view. Without the keys being in the view, the data in the underlying table can’t be inserted or updated.

So, using our earlier examples:

• empdept is not updatable because it queries two tables
• deptstats is not updatable because it uses aggregate functions
• emp_details is updatable
• employee_rst is updatable

How to Insert Data with a View

Inserting data with a view is done the same way as inserting data into a table.

For example, to insert data into the emp_details view:

1INSERT INTO emp_details (emp_id, last_name)
2VALUES (11, ‘Grover’);

This will insert another value into the employee table, and will show up in the emp_details view.

1SELECT emp_id, last_name
2FROM emp_details;

Results:

How to Update Data with a View

Updating data with a view is also done the same way as you would do it with a table.

1UPDATE employee_rst
2SET dept_id = 3
3WHERE emp_id = 6;

We’ve updated the dept_id value to 3 for employee 6. We can check this by querying the view.

1SELECT emp_id, last_name, dept_id
2FROM employee_rst;

Result:

Validating New Data Using WITH CHECK OPTION

Another feature available in views is called WITH CHECK OPTION.

When we insert or update data in a view, it updates the data in the underlying table (because the view contains no data, it’s just a query).

When we SELECT from the view again, we see the updated data.

But what if the data that we insert or update is no longer shown by the view?

For example, we could create a view that shows all employees with a salary greater than 50,000.

1CREATE VIEW emp_highsal AS
2SELECT emp_id,
3last_name,
4salary
5FROM employee
6WHERE salary > 50000;

If we SELECT from this view, we can see the results.

1SELECT emp_id,
2last_name,
3salary
4FROM emp_highsal;

Result:

Now, let’s try to insert some new data.

1INSERT INTO emp_highsal (emp_id, last_name, salary)
2VALUES (12, ‘Hill’, 45000);

The salary value is 45,000, which is less than the 50,000 that the view shows. But the data is still inserted through the view into the employee table.

1SELECT *
2FROM employee
3WHERE emp_id = 12;

Result:

So the record is in the table, but it doesn’t show up in the view:

1SELECT emp_id,
2last_name,
3salary
4FROM emp_highsal;

Result:

This might be the behaviour you want. Or, it might not be. You might want to stop data being inserted or updated if it means it does not meet the view criteria.

Luckily, Oracle has a keyword for that.

It’s called WITH CHECK OPTION.

WITH CHECK OPTION is a keyword you can add when creating a view. If you specify it when creating a view, then all inserted data and all updated data must meet the criteria in the view. If not, then the insert or update will fail.

We can recreate the view using this new keyword:

1CREATE OR REPLACE VIEW emp_highsal AS
2SELECT emp_id,
3last_name,
4salary
5FROM employee
6WHERE salary > 50000
7WITH CHECK OPTION;

Now, let’s insert a new value.

1INSERT INTO emp_highsal (emp_id, last_name, salary)
2VALUES (13, ‘Franklin’, 39000);

We can see that an error has appeared. This is because the salary value of 39000 is less than the 50000 specified in the view, and therefore does not meet the criteria.

So, that’s how you can use the WITH CHECK OPTION keyword to ensure any new data or changes to existing data still meet the criteria of your view.

What is a Materialized View?

As we learned earlier in this article, a view is an SQL statement stored on the database. No data is stored with this view, only the query.

Every time we query the view, the view’s query is then run on the underlying tables.

However, there are times where it would be useful to have data stored along with the view.

For example, if you want to find totals of orders over a time period, you would have to run a query that maybe looks at several tables, and performs some aggregate functions, and maybe with some WHERE clauses.

This query could get expensive and take a long time to run each time you wanted to get this data.

There is a way to store this data that is returned by the view.

This is what a materialized view does.

A materialized view is a view that stores the results of the view’s query.

Whenever you query the materialized view, it returns the data stored with the view itself. It doesn’t run the view’s query against the underlying tables. It’s a lot like a table.

This image may help explain it:

So, if the query that makes up the materialized view is not run when the materialized view is queried, how does the data get into the materialized view?

It’s populated when it is created. It can also be refreshed in the future.

Benefits of a Materialized View

There are several benefits to using materialized views in your database.

Improve Query Efficiency

The main benefit to using a materialized view is to improve the efficiency of a query.

If a query takes a long time to run, it could be because there are a lot of transformations being done to the data: subqueries, functions, and joins, for example.

A materialized view can combine all of that into a single result set that’s stored like a table.

This means that any user or application that needs to get this data can just query the materialized view itself, as though all of the data is in the one table, rather than running the expensive query that uses joins, functions, or subqueries.

Calculations can also be added to materialized views for any fields you may need, which can save time, and are often not stored in the database. For example, perhaps you want to store both the monthly and annual salary for an employee. Normally, you would only store one and calculate the other, but in a materialized view, you can store both.

Materialized views can also be created to optimise a single query. They don’t need to be normalized and fit into the overall database design in that way.

I worked on a client project where one of the database developers changed the views to a materialized view and saw a large increase in performance. A view that was taking 10 minutes to run was only taking 20 seconds to run when it was converted to a materialized view.

Simplify a Query

Like a regular view, a materialized view can also be used to simplify a query. If a query is using a lot of logic such as joins and functions, using a materialized view can help remove some of that logic and place it into the materialized view.

Disadvantages of a Materialized View

Updates to Data Need to be Set Up

The main disadvantage to using materialized views is that the data needs to be refreshed.

The data that’s used to populate the materialized view is stored in the database tables. These tables can have their data updated, inserted, or deleted. When that happens, the data in the materialized view needs to be updated.

This can be done manually, but it should be done automatically.

Data May Be Inconsistent

Because the data is stored separately in the materialized view, the data in the materialized view may be inconsistent with the data in the underlying tables.

This may be an issue if you are expecting or relying on data to be consistent.

However, for scenarios where it doesn’t matter (e.g. monthly reporting on months in the past), then it may be OK.

What’s the Difference Between a Materialized View and a Table?

If you’ve read the description of a materialized view, you might be wondering how it’s different from a table.

A database object that stores data so it can be queried - sounds just like a table.

But it is different.

The main difference is how it is refreshed.

If you wanted to refresh a table with data from other tables, you would need to find and use the original script to update the data in the table, or use a MERGE statement.

With a materialized view, you just run a command to refresh it. The materialized view knows what to do, as it has the query that is used to populate the data stored with it.

What’s the Difference Between a View and a Materialized View?

Views are only a stored query and run the query each time the view is accessed.

Materalized views have data stored on the disk and the data is updated occasionally based on the definition of the view.

This table helps to explain some of the differences.

How to Create a Materialized View

To create a materialized view, we use the following syntax:

1CREATE MATERIALIZED VIEW viewname
2[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]
3[BUILD IMMEDIATE|BUILD DEFERRED]
4AS
5select_query;

This includes:

• CREATE MATERIALIZED VIEW: specify what’s being created.
• viewname: the name of the new materialized view.
• REFRESH FAST: uses an incremental refresh method which uses changes made to the underlying tables in a log file.
• REFRESH COMPLETE: uses a complete refresh by re-running the query in the materialized view.
• REFRESH FORCE: indicates that a fast refresh should be performed if possible, but if not, a complete refresh is performed. Force is the default (between Fast, Force, and Complete)
• REFRESH ON DEMAND: indicates that a refresh will occur manually whenever specific package functions are called. On Demand is the default.
• REFRESH ON COMMIT: indicates that a fast refresh occurs whenever a transaction commits that makes changes to any of the underlying tables.
• BUILD IMMEDIATE: the materialized view will be populated immediately. This is the default.
• BUILD DEFERRED: the materialized view is populated on the next refresh operation.
• AS select_query: the query that is run, with the results stored in the materialized view.

The syntax above is just a small extract of the possible syntax. Oracle’s documentation lists a lot of other features that are available in materialized views.

For other databases, refer to the documentation TODO add links:

• Oracle
• SQL Server
• PostgreSQL
• MySQL

Examples of Materialized Views

Let’s look at some examples of materialized views.

View with Joins

We can use the same query as the example from the View section.

1CREATE MATERIALIZED VIEW empdept AS
2SELECT
3e.emp_id,
4e.last_name,
5e.salary,
6d.dept_id,
7d.dept_name
8FROM employee e
9INNER JOIN department d ON e.dept_id = d.dept_id;

The materialized view empdept can now be queried:

1SELECT emp_id,
2last_name,
3salary,
4dept_id,
5dept_name
6FROM empdept;

Result:

View with Aggregate Functions

This example uses aggregate functions, and is the same query as earlier in this guide

 1CREATE MATERIALIZED VIEW dept_stats
 2REFRESH COMPLETE
 3AS
 4SELECT
 5d.dept_id,
 6d.dept_name,
 7COUNT(e.*) AS emp_count,
 8SUM(e.salary) AS total_salary
 9FROM department d
10INNER JOIN employee e ON d.dept_id = e.dept_id
11GROUP BY d.dept_id, d.dept_name;

Now, to find this data, all we need to do is query the dept_stats view.

1SELECT dept_id,
2dept_name
3emp_count
4total_salary
5FROM dept_stats;

Result:

The data is stored in the materialized view so this can be queried more efficiently.

What Are Inline Views?

You might have heard of the term “inline view” before.

What does that mean?

An inline view is a SELECT query that’s used within the FROM clause of another SELECT query. It’s treated like a table but no view object is created. They are used to simplify queries by consolidating your query and removing join operations.

If you’re familiar with SQL Server and PostgreSQL, they are called “derived tables” in SQL Server and subselects in PostgreSQL.

Inline views look like this:

1SELECT columns
2FROM (inline_view);

The inline_view is another SELECT query.

They are helpful because we don’t need to create a view or a temporary table just for a single query.

Examples of Inline Views

Here’s an example of an inline view using our employee and department tables.

1SELECT dept_id, emp_salary
2FROM (
3  SELECT e.dept_id,
4  SUM(e.salary) AS emp_salary
5  FROM employee e
6  GROUP BY e.dept_id) AS emp_data
7ORDER BY emp_salary DESC;

Results:

This query will show all of the department IDs and the SUM of salary for each of them. It can be written in other ways, but this is an example of writing it using an inline view.

Let’s look at another example, where we find the average salary for all employees in departments, where the employees are earning more than a certain amount.

1SELECT d.dept_name,
2AVG(ed.salary) AS avg_salary
3FROM (
4  SELECT e.id,
5  e.dept_id,
6  e.salary
7  FROM employee e
8  WHERE e.salary > 30000) AS empdata ed
9INNER JOIN department d ON ed.dept_id = d.dept_id;

Results:

This shows us the department names and average salaries for each department, for employees with a salary more than 30,000.

Best Practices for Views and Materialized Views

When you’re creating views or materialised views, here are some tips to keep in mind:

• Consider if performance or having up-to-date data is more important when deciding between a view and a materialised view. If performance is more important, use a materialised view. If having up-to-date data is more important, consider a view.
• Add calculated columns to a materialised view if they are used often. This means your queries will not have to perform the calculation as they already exist as columns.
• If you’re accessing external databases using a database link, consider using views or materialised views to hide this fact and make queries simpler.
• Avoid writing views that query other views. If a view needs to be built on another view, then use the base table instead. Otherwise, the impact of changing a table can be hard to work out and can cause maintenance issues.
• Look for commonly used WITH clauses (or Common Table Expressions), and consider creating them as views if they are used often.

If you want to know if a table (or view) exists before creating a view, so you can check for clashes with existing objects, you can read this article here to find some SQL queries to run.

Conclusion

Views are helpful objects in a database that can be used to simplify your queries and improve the security of your system. Materalised views are also very useful, helping simplify queries and improve the performance of queries by storing data in an easy-to-use object.

Overusing views can cause issues for your database by making things more complicated, so be careful when creating too many of them, but using them where needed can be beneficial.

Do you have any questions about views? Let me know in the comments below.