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:

CREATE [OR REPLACE] VIEW viewname AS
SELECT 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:

emp_id	last_name	dept_id	salary
1	Jones	3	23000
2	Smith	2	49000
3	King	1	21000
4	Johnson	4	40000
5	Archer	1	93000
6	McDonald	4	12000
7	Ferrier	3	42000
8	Reacher	2	37000
9	Clarkson	4	52000
10	Simpson	1	60000

Department:

dept_id	dept_name
1	Sales
2	Customer Service
3	Finance
4	IT

 

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.

CREATE VIEW emp_details AS
SELECT
emp_id,
last_name
FROM employee;

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

SELECT emp_id, last_name
FROM emp_details;

Result:

emp_id	last_name
1	Jones
2	Smith
3	King
4	Johnson
5	Archer
6	McDonald
7	Ferrier
8	Reacher
9	Clarkson
10	Simpson

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.

CREATE VIEW employee_rst AS
SELECT
emp_id,
last_name,
dept_id
FROM 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:

SELECT emp_id,
last_name,
dept_id
FROM employee_rst;

emp_id	last_name	dept_id
1	Jones	3
2	Smith	2
3	King	1
4	Johnson	4
5	Archer	1
6	McDonald	4
7	Ferrier	3
8	Reacher	2
9	Clarkson	4
10	Simpson	1

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.

CREATE VIEW empdept AS
SELECT
e.emp_id,
e.last_name,
e.salary,
d.dept_id,
d.dept_name
FROM employee e
INNER 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.

SELECT emp_id,
last_name,
salary,
dept_id,
dept_name
FROM empdept;

Result:

emp_id	name	salary	dept_id	dept_name
1	Jones	23000	3	Finance
2	Smith	49000	2	Customer Service
3	King	21000	1	Sales
4	Johnson	40000	4	IT
5	Archer	93000	1	Sales
6	McDonald	12000	4	IT
7	Ferrier	42000	3	Finance
8	Reacher	37000	2	Customer Service
9	Clarkson	52000	4	IT
10	Simpson	60000	1	Sales

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:

SELECT
d.dept_id,
d.dept_name,
COUNT(e.*) AS emp_count,
SUM(e.salary) AS total_salary
FROM department d
INNER JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

Result:

dept_id	dept_name	emp_count	total_salary
1	Sales	3	174000
2	Customer Service	2	86000
3	Finance	2	65000
4	IT	3	104000

This shows the result we want.

Now let’s turn this into a view.

CREATE VIEW dept_stats AS
SELECT
d.dept_id,
d.dept_name,
COUNT(e.*) AS emp_count,
SUM(e.salary) AS total_salary
FROM department d
INNER JOIN employee e ON d.dept_id = e.dept_id
GROUP 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.

SELECT dept_id,
dept_name,
emp_count,
total_salary
FROM dept_stats;

Result:

dept_id	dept_name	emp_count	total_salary
1	Sales	3	174000
2	Customer Service	2	86000
3	Finance	2	65000
4	IT	3	104000

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

SELECT dept_id,
dept_name,
emp_count,
total_salary
FROM dept_stats
WHERE dept_id = 2;

Result:

dept_id	dept_name	emp_count	total_salary
2	Customer Service	2	86000

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:

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

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

SELECT emp_id, last_name
FROM emp_details;

Results:

emp_id	last_name
1	Jones
2	Smith
3	King
4	Johnson
5	Archer
6	McDonald
7	Ferrier
8	Reacher
9	Clarkson
10	Simpson
11	Grover

 

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.

UPDATE employee_rst
SET dept_id = 3
WHERE emp_id = 6;

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

SELECT emp_id, last_name, dept_id
FROM employee_rst;

Result:

emp_id	last_name	dept_id
1	Jones	3
2	Smith	2
3	King	1
4	Johnson	4
5	Archer	1
6	McDonald	3
7	Ferrier	3
8	Reacher	2
9	Clarkson	4
10	Simpson	1

 

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.

CREATE VIEW emp_highsal AS
SELECT emp_id,
last_name,
salary
FROM employee
WHERE salary > 50000;

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

SELECT emp_id,
last_name,
salary
FROM emp_highsal;

Result:

emp_id	last_name	salary
5	Archer	93000
9	Clarkson	52000
10	Simpson	60000

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

INSERT INTO emp_highsal (emp_id, last_name, salary)
VALUES (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.

SELECT *
FROM employee
WHERE emp_id = 12;

Result:

emp_id	last_name	dept_id	salary
5	Archer	NULL	45000

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

SELECT emp_id,
last_name,
salary
FROM emp_highsal;

Result:

emp_id	last_name	salary
5	Archer	93000
9	Clarkson	52000
10	Simpson	60000

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:

CREATE OR REPLACE VIEW emp_highsal AS
SELECT emp_id,
last_name,
salary
FROM employee
WHERE salary > 50000
WITH CHECK OPTION;

Now, let’s insert a new value.

INSERT INTO emp_highsal (emp_id, last_name, salary)
VALUES (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.

Component	View	Materialized View
Stored on database	SQL query definition	SQL query definition, and data that is returned by it
Data storage	None – remains in underlying tables	Stored within the view
Refresh	Latest data is returned whenever the query is run	Data stored in view may not be the most up-to-date. Options can be set to update the materialized view data periodically.
Performance	Same as underlying query	Usually faster than a view.
Indexes	None – uses same indexes as underlying tables	Can be created on the materialized view itself
Space Used	Small, as it is only the SQL query definition	Can be large, as the data is stored with the object

 

How to Create a Materialized View

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

CREATE MATERIALIZED VIEW viewname
[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]
[BUILD IMMEDIATE|BUILD DEFERRED]
AS
select_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.

CREATE MATERIALIZED VIEW empdept AS
SELECT
e.emp_id,
e.last_name,
e.salary,
d.dept_id,
d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;

The materialized view empdept can now be queried:

SELECT emp_id,
last_name,
salary,
dept_id,
dept_name
FROM empdept;

Result:

emp_id	name	salary	dept_id	dept_name
1	Jones	23000	3	Finance
2	Smith	49000	2	Customer Service
3	King	21000	1	Sales
4	Johnson	40000	4	IT
5	Archer	93000	1	Sales
6	McDonald	12000	4	IT
7	Ferrier	42000	3	Finance
8	Reacher	37000	2	Customer Service
9	Clarkson	52000	4	IT
10	Simpson	60000	1	Sales

 

View with Aggregate Functions

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

CREATE MATERIALIZED VIEW dept_stats
REFRESH COMPLETE
AS
SELECT
d.dept_id,
d.dept_name,
COUNT(e.*) AS emp_count,
SUM(e.salary) AS total_salary
FROM department d
INNER JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

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

SELECT dept_id,
dept_name
emp_count
total_salary
FROM dept_stats;

Result:

dept_id	dept_name	emp_count	total_salary
1	Sales	3	174000
2	Customer Service	2	86000
3	Finance	2	65000
4	IT	3	104000

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:

SELECT columns
FROM (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.

SELECT dept_id, emp_salary
FROM (
  SELECT e.dept_id,
  SUM(e.salary) AS emp_salary
  FROM employee e
  GROUP BY e.dept_id) AS emp_data
ORDER BY emp_salary DESC;

Results:

dept_id	emp_salary
1	174000
4	104000
2	86000
3	65000

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.

SELECT d.dept_name,
AVG(ed.salary) AS avg_salary
FROM (
  SELECT e.id,
  e.dept_id,
  e.salary
  FROM employee e
  WHERE e.salary > 30000) AS empdata ed
INNER JOIN department d ON ed.dept_id = d.dept_id;

Results:

dept_id	avg_salary
1	76500
2	43000
3	42000
4	46000

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.