FB

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

Table of Contents

Here’s what we will be covering in this guide. Click on any of the headings and it will take you to that section on this page.

  1. What is a View?
  2. Why Use a View?
  3. How to Create a View in SQL
  4. Examples of Creating a View
  5. Inserting or Updating Data in a View
  6. What is a Materialized View?
  7. Benefits of a Materialized View
  8. Disadvantages of a Materialized View
  9. What’s the Difference Between a View and a Materialized View?
  10. How to Create a Materialized View
  11. Examples of Materialized Views
  12. What Are Inline Views?
  13. Examples of Inline Views
  14. Best Practices for Views and Materialized Views
  15. Conclusion

 

What is a View?

(Back to Top)

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.

SQL View

 

Why Use a View?

(Back to Top)

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).

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

 

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.

SQL Views and Security

 

How to Create a View in SQL

(Back to Top)

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

(Back to Top)

Sample Data

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

Employee:

emp_idlast_namedept_idsalary
1Jones323000
2Smith249000
3King121000
4Johnson440000
5Archer193000
6McDonald412000
7Ferrier342000
8Reacher237000
9Clarkson452000
10Simpson160000

Department:

dept_iddept_name
1Sales
2Customer Service
3Finance
4IT

 

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_idlast_name
1Jones
2Smith
3King
4Johnson
5Archer
6McDonald
7Ferrier
8Reacher
9Clarkson
10Simpson

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_idlast_namedept_id
1Jones3
2Smith2
3King1
4Johnson4
5Archer1
6McDonald4
7Ferrier3
8Reacher2
9Clarkson4
10Simpson1

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_idnamesalarydept_iddept_name
1Jones230003Finance
2Smith490002Customer Service
3King210001Sales
4Johnson400004IT
5Archer930001Sales
6McDonald120004IT
7Ferrier420003Finance
8Reacher370002Customer Service
9Clarkson520004IT
10Simpson600001Sales

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_iddept_nameemp_counttotal_salary
1Sales3174000
2Customer Service286000
3Finance265000
4IT3104000

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_iddept_nameemp_counttotal_salary
1Sales3174000
2Customer Service286000
3Finance265000
4IT3104000

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_iddept_nameemp_counttotal_salary
2Customer Service286000

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

(Back to Top)

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 analytic 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 updateable because it queries two tables
  • deptstats is not updateable because it uses aggregate functions
  • emp_details is updateable
  • employee_rst is updateable

 

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_idlast_name
1Jones
2Smith
3King
4Johnson
5Archer
6McDonald
7Ferrier
8Reacher
9Clarkson
10Simpson
11Grover

 

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_idlast_namedept_id
1Jones3
2Smith2
3King1
4Johnson4
5Archer1
6McDonald3
7Ferrier3
8Reacher2
9Clarkson4
10Simpson1

 

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_idlast_namesalary
5Archer93000
9Clarkson52000
10Simpson60000

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_idlast_namedept_idsalary
5ArcherNULL45000

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_idlast_namesalary
5Archer93000
9Clarkson52000
10Simpson60000

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?

(Back to Top)

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:

Materialized View

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

(Back to Top)

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

(Back to Top)

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?

(Back to Top)

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?

(Back to Top)

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.

ComponentViewMaterialized View
Stored on databaseSQL query definitionSQL query definition, and data that is returned by it
Data storageNone – remains in underlying tablesStored within the view
RefreshLatest data is returned whenever the query is runData stored in view may not be the most up-to-date. Options can be set to update the materialized view data periodically.
PerformanceSame as underlying queryUsually faster than a view.
IndexesNone – uses same indexes as underlying tablesCan be created on the materialized view itself
Space UsedSmall, as it is only the SQL query definitionCan be large, as the data is stored with the object

 

How to Create a Materialized View

(Back to Top)

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

(Back to Top)

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_idnamesalarydept_iddept_name
1Jones230003Finance
2Smith490002Customer Service
3King210001Sales
4Johnson400004IT
5Archer930001Sales
6McDonald120004IT
7Ferrier420003Finance
8Reacher370002Customer Service
9Clarkson520004IT
10Simpson600001Sales

 

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_iddept_nameemp_counttotal_salary
1Sales3174000
2Customer Service286000
3Finance265000
4IT3104000

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

 

What Are Inline Views?

(Back to Top)

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

(Back to Top)

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_idemp_salary
1174000
4104000
286000
365000

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_idavg_salary
176500
243000
342000
446000

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

(Back to Top)

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.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Get Your SQL Cheat Sheets Now: