SQL Views: CREATE VIEW, When to Use One, and What It Costs

A view is a stored query that behaves like a table. You give a SELECT a name, and from then on anyone can select from that name as though it were a real table. Nothing is copied — the query runs again each time the view is used.

That last sentence is the one people get wrong, and it decides everything else: what views are good for, why they do not speed anything up, and what a materialized view is for. Every example below runs.

Creating and using one

A view over the employees table that hides the inactive rows:

CREATE VIEW active_staff AS
  SELECT first_name, last_name, dept_id
  FROM Employees
  WHERE active = 1;

SELECT * FROM active_staff;

From here on active_staff can be used anywhere a table can. You can filter it, sort it, join it, and select from it inside another query:

CREATE VIEW high_earners AS
  SELECT first_name, salary
  FROM Employees
  WHERE salary > 100000;

SELECT * FROM high_earners
WHERE salary > 130000
ORDER BY salary DESC;

What actually happens: the database substitutes the view’s query into yours and optimises the combination, so that second query becomes a single scan with both conditions. You get the readability of two steps and the performance of one.

The genuinely useful case: hiding a complicated join

This is where views earn their place. A three-table join that everybody needs and nobody wants to retype:

CREATE VIEW order_lines AS
  SELECT o.order_id,
         c.company,
         p.name AS product,
         o.quantity,
         o.order_total,
         o.status
  FROM Orders o
  JOIN Customers c ON c.customer_id = o.customer_id
  JOIN Products  p ON p.product_id = o.product_id;

SELECT company, product, order_total
FROM order_lines
WHERE status = 'Shipped'
ORDER BY order_total DESC
LIMIT 5;

The join logic is written once and correct once. If the schema changes — a new key, a renamed column — you fix the view and every query built on it keeps working. That is the real argument for views: they are the database’s way of not repeating yourself.

The same applies to aggregates that several reports share:

CREATE VIEW dept_summary AS
  SELECT d.dept_name,
         COUNT(e.emp_id)        AS headcount,
         ROUND(AVG(e.salary), 0) AS avg_salary
  FROM Departments d
  LEFT JOIN Employees e ON e.dept_id = d.dept_id
  GROUP BY d.dept_name;

SELECT * FROM dept_summary
ORDER BY headcount DESC;

A view does not make anything faster

If the underlying query is slow, the view is slow, and the fix is the usual one: index the columns being filtered and joined on. A view can even make things slower when it is over-general — a five-table view read for one column still has to be resolved, and a database will not always eliminate joins it cannot prove are unnecessary.

Materialized views: the one that does store data

A materialized view runs its query once and keeps the result on disk, so reading it is as cheap as reading a table. The cost is that the stored result goes stale and has to be refreshed:

CREATE MATERIALIZED VIEW dept_summary_cached AS
  SELECT d.dept_name, COUNT(e.emp_id) AS headcount
  FROM Departments d
  LEFT JOIN Employees e ON e.dept_id = d.dept_id
  GROUP BY d.dept_name;

REFRESH MATERIALIZED VIEW dept_summary_cached;

MySQL has no materialized views at all, which is why so much MySQL code uses a summary table refreshed by a scheduled job — the same idea, built by hand.

Can you update a view?

Sometimes. If a view maps simply onto one table — a plain column list, one table, no aggregate, no DISTINCT, no GROUP BY, no join — most databases let you insert, update and delete through it, and the change lands in the underlying table.

As soon as the view aggregates or joins, the mapping is ambiguous: if a row in dept_summary says a department has five people, there is no sensible way to interpret “change that to six”. Such views are read-only, and the database will say so. PostgreSQL offers INSTEAD OF triggers to define the behaviour yourself, which is powerful and usually a sign that the application should be writing to the tables directly.

One more thing worth knowing: WITH CHECK OPTION stops an update through a view from producing a row the view could no longer see — changing an active employee to inactive through active_staff, for example. Without it, the row silently vanishes from the view it was written through.

Views for permissions

A view is also a security boundary. Grant a reporting account access to a view that excludes the salary column, and the account can answer questions about headcount without ever being able to read pay:

CREATE VIEW staff_directory AS
  SELECT first_name, last_name, dept_id
  FROM Employees;

SELECT * FROM staff_directory
ORDER BY dept_id, last_name;

Then GRANT SELECT ON staff_directory TO reporting with no grant on Employees itself. The account can see exactly what the view exposes and nothing more. This is one of the neatest uses of views and one of the least used — see the DCL section on GRANT and REVOKE.

Housekeeping

DROP VIEW removes it, and because a view holds no data nothing is lost but the definition:

CREATE VIEW temp_check AS SELECT COUNT(*) AS employees FROM Employees;
SELECT * FROM temp_check;

DROP VIEW temp_check;

SELECT COUNT(*) AS still_here FROM Employees;

Two habits worth keeping. Name views so they cannot be mistaken for tables — a v_ prefix or a plainly descriptive name — because the next person will assume a table is a table. And avoid building views on views more than a layer or two deep: it reads nicely and then one day you are debugging a query whose real cost is six joins you cannot see.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Put a name to the departmentMedium
• Every department, even the quiet onesHard
• Revenue by product categoryHard
• Category revenue reportHard

See all 42 exercises →

Frequently asked questions

Related reading

• Database normalization: 1NF, 2NF and 3NF
• SQL indexes: making the underlying query fast
• CTEs: the same idea for one query only
• SQL commands: DDL, DML, DCL and TCL
• SQL JOINs explained