Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 40 ยท Joins and Subqueries

Views in MariaDB

Save queries as views in MariaDB with CREATE VIEW and CREATE OR REPLACE, updatable views, WITH CHECK OPTION, MERGE and TEMPTABLE algorithms, SQL SECURITY, SHOW CREATE VIEW and the limits of views.

A view is a saved SELECT statement with a name. You query it like a table, but it stores no data of its own: each time you read it, MariaDB runs the underlying query. Views let you hide complex joins, give reports a stable interface, restrict which columns or rows a user may see and keep application code simple while the schema changes underneath.

Creating a view

SQL
CREATE VIEW customer_orders AS
SELECT c.id AS customer_id,
       c.first_name,
       c.last_name,
       COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.first_name, c.last_name;

SELECT * FROM customer_orders WHERE orders > 3;

From the outside, customer_orders looks like a table with four columns. Use CREATE OR REPLACE VIEW (a MariaDB convenience) to change the definition in one step, and DROP VIEW IF EXISTS to remove it. Views live in a database like tables do: SHOW FULL TABLES WHERE Table_type = 'VIEW', and SHOW CREATE VIEW customer_orders\G prints its definition.

Try the same pattern here. SQLite's views behave the same for reading:

SQL Create and query a view ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You can give the view column names explicitly, which is handy when the select list has expressions:

SQL
CREATE VIEW product_prices (product, gross) AS
SELECT name, ROUND(price * 1.2, 2) FROM products;

Why use views?

  1. Simplicity. Write a complicated four-table join once, then SELECT from the view.
  2. Consistency. Every report uses the same definition of "active customer" or "net revenue".
  3. Security. Expose some columns or rows without granting access to the base table (for example, hide salary).
  4. Compatibility. When a table is restructured, the view can keep the old shape for existing code.
SQL A view that hides sensitive columns ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In MariaDB you would then GRANT SELECT ON company.employee_directory TO 'hr_viewer'@'%' and give that user no rights on employees itself.

Updatable views and WITH CHECK OPTION

A simple view, one table with no GROUP BY, DISTINCT, aggregates or UNION, can be used with INSERT, UPDATE and DELETE, and the changes go to the base table:

SQL
CREATE VIEW in_stock AS
SELECT id, name, price, stock FROM products WHERE stock > 0
WITH CHECK OPTION;

UPDATE in_stock SET price = price * 1.05 WHERE id = 3;          -- works
UPDATE in_stock SET stock = 0 WHERE id = 3;                      -- error with CHECK OPTION

Without WITH CHECK OPTION you could update a row so it no longer satisfies the view's WHERE, and it would vanish from the view. The option rejects such changes, so the view can only create or change rows that remain visible through it. Use WITH LOCAL CHECK OPTION to check only the view's own condition and not underlying views.

Views with joins can be updatable if each statement touches only one base table. Views with aggregates or DISTINCT are read-only.

The browser engine does not allow writes to views directly, so here is how the idea looks by reading only:

SQL What a view returns versus its base table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

ALGORITHM: MERGE, TEMPTABLE, UNDEFINED

MariaDB can process a view in two ways:

  • MERGE: the view's query is merged into the outer query, so the optimizer sees one statement and can use indexes. This is fast and also makes the view updatable.
  • TEMPTABLE: the view is first materialized into a temporary table, then queried. Used when merging is impossible (aggregates, DISTINCT, LIMIT, UNION).
  • UNDEFINED (default): MariaDB picks MERGE when it can.
SQL
CREATE ALGORITHM = MERGE VIEW cheap_products AS SELECT * FROM products WHERE price < 20;

Specifying MERGE makes the statement fail if merging is not possible, a useful guarantee. A view with GROUP BY is evaluated as TEMPTABLE, which is why filtering a big aggregated view can be slow: the whole aggregation runs before your WHERE applies. Test with EXPLAIN.

SQL SECURITY and definers

Each view has a definer (the account that created it) and a security mode:

SQL
CREATE DEFINER = 'admin'@'localhost' SQL SECURITY DEFINER VIEW salaries_summary AS ...;
CREATE SQL SECURITY INVOKER VIEW my_view AS ...;
  • DEFINER (default): the view runs with the creator's privileges. This lets users read through a view even if they have no rights on the base tables, which is the security feature described above.
  • INVOKER: the view runs with the privileges of whoever queries it.

Because definer views run with the creator's rights, avoid creating them as root and review who may query them. If the definer account is dropped, the view stops working with an error.

Limits and good practice

  • Views cannot have an index of their own, though queries use the base table's indexes when merged. MariaDB has no materialized views; for expensive aggregates build a summary table and refresh it with an event or scheduled job.
  • Avoid stacking many views on views; plans get hard to read.
  • SELECT * in a view definition freezes the column list at creation. Add a column to the table later and the view does not show it until you recreate it. List columns explicitly.
  • A view's ORDER BY is ignored when the outer query has its own ordering. Sort in the outer query.
  • Rename or drop columns used by a view and the view breaks at query time. SHOW CREATE VIEW and information_schema.views tell you what exists, and CHECK TABLE view_name finds broken ones.
flowchart LR
  A["Application query"] --> V["View: saved SELECT"]
  V --> B1["customers"]
  V --> B2["orders"]

A view stores a query, not data; the base tables are read each time.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does a view store?

    A view stores its definition. The underlying query runs each time you read it.
  2. 2. What does WITH CHECK OPTION do?

    It keeps changes consistent with the view's WHERE clause.
  3. 3. Which view type can usually NOT be updated?

    Aggregated, DISTINCT and UNION views have no one-to-one mapping to base rows.
  4. 4. Which algorithm lets the optimizer merge the view into the outer query?

    MERGE rewrites the outer query to use the base tables directly, which allows index use.
  5. 5. Why avoid SELECT * in a view definition?

    The column list is expanded and stored when the view is created.

Summary

  • A view is a named, stored SELECT: it simplifies queries, standardizes definitions and restricts access.
  • Simple single-table views are updatable; WITH CHECK OPTION keeps changes visible through the view.
  • MERGE lets the optimizer see through the view, TEMPTABLE materializes it; SQL SECURITY DEFINER/INVOKER controls whose privileges apply.
  • No materialized views in MariaDB: use summary tables when you need stored results.

Next: Recursive CTEs and hierarchical queries