MySQL Views: How and why with examples
MySQL allows you to define views on your databases that let you write complex queries you can select from just like they were tables. This tutorial will teach you how to use views and give you some ideas about what they're good for.
What's a view?
A view is a SELECT query that you give a name and can then query like it was a table. Here's a
simple classic example using MySQL create view
syntax: defining a view that joins two
tables.
CREATE TABLE customers (
id bigint primary key auto_increment,
first_name varchar(100),
last_name varchar(100),
country_code varchar(2));
CREATE TABLE orders (
order_id bigint primary key auto_increment,
cust_id bigint,
order_date datetime default now(),
constraint foreign key (cust_id) references customers(id)
);
INSERT INTO customers (first_name, last_name, country_code) VALUES ("tim", "sehn", "ca");
INSERT INTO customers (first_name, last_name, country_code) VALUES ("aaron", "son", "us");
INSERT INTO customers (first_name, last_name, country_code) VALUES ("brian", "hendriks", "us");
-- 2 order per customer
INSERT INTO orders (cust_id) VALUES (1), (1), (2), (2), (3), (3);
CREATE VIEW customer_orders
(id, first_name, last_name, country_code, order_id, cust_id, order_date)
AS SELECT id, first_name, last_name, country_code, order_id, cust_id, order_date
FROM CUSTOMERS c JOIN orders o
ON c.id = o.cust_id;
SELECT * FROM customer_orders;
+----+------------+-----------+--------------+----------+---------+---------------------+
| id | first_name | last_name | country_code | order_id | cust_id | order_date |
+----+------------+-----------+--------------+----------+---------+---------------------+
| 1 | tim | sehn | ca | 1 | 1 | 2024-01-25 14:54:26 |
| 1 | tim | sehn | ca | 2 | 1 | 2024-01-25 14:54:26 |
| 2 | aaron | son | us | 3 | 2 | 2024-01-25 14:54:26 |
| 2 | aaron | son | us | 4 | 2 | 2024-01-25 14:54:26 |
| 3 | brian | hendriks | us | 5 | 3 | 2024-01-25 14:54:26 |
| 3 | brian | hendriks | us | 6 | 3 | 2024-01-25 14:54:26 |
+----+------------+-----------+--------------+----------+---------+---------------------+
6 rows in set (0.01 sec)This view joins the customers and orders tables on their foreign key. Selecting from
customer_orders is equivalent to running the JOIN query.
Let's break down the definition of this view and see what each part does.
Defining a view
The view we defined above looks like this. I'm commenting every element in the definition to make it clear what it does.
CREATE VIEW -- SQL syntax to define a view
customer_orders -- The name of the view, which must be unique
(id, first_name, last_name, country_code, order_id, order_date) -- an optional list of column names for the result
AS -- SQL keyword to begin the view definition
SELECT * FROM CUSTOMERS c JOIN orders o ON c.id = o.cust_id; -- the view definitionSo a view has a number of parts that must be declared in the order above. They are:
CREATE VIEW. This is the SQL syntax to create a new view. You can also include theOR REPLACEclause here to automatically update a view instead of creating it if it already exists.- The name of the view. View names must be unique and can't clash with table names.
- Optionally, the list of columns names for the result set.
AS, followed by aSELECTstatement. This is theSELECTstatement that gets run when you query the view.
Examining views in a database
There are several ways to find the views in a database and see what they do. MySQL provides several handy pieces of syntax for doing this.
The simplest method is the statement SHOW FULL TABLES:
mysql> show full tables;
+-------------------+------------+
| Tables_in_test | Table_type |
+-------------------+------------+
| customer_orders | VIEW |
| customers | BASE TABLE |
| orders | BASE TABLE |
+-------------------+------------+Views are designated as such in the Table_type column. Otherwise, views appear as normal tables to
most inspections. If you leave out the FULL keyword above, you won't be able to tell the
difference between views and normal tables.
mysql> show tables;
+-------------------+
| Tables_in_test |
+-------------------+
| customer_orders |
| customers |
| orders |
+-------------------+To see what a view does, you can ask MySQL to DESCRIBE it, or use the DESC shortcut.
DESC customer_orders;
+--------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-------------------+
| id | bigint | NO | | 0 | |
| first_name | varchar(100) | YES | | NULL | |
| last_name | varchar(100) | YES | | NULL | |
| country_code | varchar(2) | YES | | NULL | |
| order_id | bigint | NO | | 0 | |
| cust_id | bigint | YES | | NULL | |
| order_date | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-------------------+-------------------+Note that the column types here have things like NULL constraints and DEFAULT values, since they
come directly from their underlying tables. Depending on how you define your view, the result
columns might not have those details.
To get the full definition of a view you can use the SHOW CREATE VIEW statement:
mysql> SHOW CREATE VIEW customer_orders;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View
| character_set_client | collation_connection |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| customer_orders | CREATE ALGORITHM=UNDEFINED DEFINER=`zachmu`@`%` SQL SECURITY DEFINER VIEW `customer_orders` AS select `c`.`id` AS `id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`country_code` AS `country_code`,`o`.`order_id` AS `order_id`,`o`.`cust_id` AS `cust_id`,`o`.`order_date` AS `order_date` from (`customers` `c` join `orders` `o` on((`c`.`id` = `o`.`cust_id`))) | utf8mb4 | utf8mb4_0900_ai_ci |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+The CREATE VIEW statement generated by MySQL includes a bunch of optional syntax that we didn't
bother to use when creating our view, and for most use cases you shouldn't need to. You can always
read the full docs if you want to know
what these optional clauses do.
Finally, the information_schema database can show additional information about views as well:
select * from views where table_name = 'customer_orders'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: customer_orders
VIEW_DEFINITION: select `c`.`id` AS `id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`country_code` AS `country_code`,`o`.`order_id` AS `order_id`,`o`.`cust_id` AS `cust_id`,`o`.`order_date` AS `order_date` from (`test`.`customers` `c` join `test`.`orders` `o` on((`c`.`id` = `o`.`cust_id`)))
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: zachmu@%
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ciUsing views in queries
In your queries, you can mostly treat views just like normal tables. This includes things like
applying a WHERE clause to them:
select * from customer_orders where country_code = 'ca';
+----+------------+-----------+--------------+----------+---------+---------------------+
| id | first_name | last_name | country_code | order_id | cust_id | order_date |
+----+------------+-----------+--------------+----------+---------+---------------------+
| 1 | tim | sehn | ca | 1 | 1 | 2024-01-25 14:54:26 |
| 1 | tim | sehn | ca | 2 | 1 | 2024-01-25 14:54:26 |
+----+------------+-----------+--------------+----------+---------+---------------------+Or using them as a table in a join:
select * from orders o join customer_orders co on o.order_id = co.cust_id where country_code = "ca";
+----------+---------+---------------------+----+------------+-----------+--------------+----------+---------+---------------------+
| order_id | cust_id | order_date | id | first_name | last_name | country_code | order_id | cust_id | order_date |
+----------+---------+---------------------+----+------------+-----------+--------------+----------+---------+---------------------+
| 1 | 1 | 2024-01-25 14:54:26 | 1 | tim | sehn | ca | 1 | 1 | 2024-01-25 14:54:26 |
| 1 | 1 | 2024-01-25 14:54:26 | 1 | tim | sehn | ca | 2 | 1 | 2024-01-25 14:54:26 |
+----------+---------+---------------------+----+------------+-----------+--------------+----------+---------+---------------------+(The above example is a bit silly since we're not getting any additional information from the join, but it works fine).
Or you can use them in a sub-select, like this query that finds every customer that hasn't ordered anything in the US:
select first_name, last_name from customers
where id not in
(select cust_id from customer_orders where country_code = "us");
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tim | sehn |
+------------+-----------+Basically, anywhere in a query where a table might appear, you can use a view instead and it should work.
Views referenced by other views
If we want to, we can turn our query for US customers above into its own view, which references the
underlying customer_orders view. This is as easy as another CREATE VIEW statement:
create view us_customer_orders as select * from customer_orders where country_code = 'us';
Query OK, 0 rows affected (0.01 sec)
select * from us_customer_orders;
+----+------------+-----------+--------------+----------+---------+---------------------+
| id | first_name | last_name | country_code | order_id | cust_id | order_date |
+----+------------+-----------+--------------+----------+---------+---------------------+
| 2 | aaron | son | us | 3 | 2 | 2024-01-25 14:54:26 |
| 2 | aaron | son | us | 4 | 2 | 2024-01-25 14:54:26 |
| 3 | brian | hendriks | us | 5 | 3 | 2024-01-25 14:54:26 |
| 3 | brian | hendriks | us | 6 | 3 | 2024-01-25 14:54:26 |
+----+------------+-----------+--------------+----------+---------+---------------------+These nested views can always be expanded with the original view definition, just like any other
view. If we expand out the query select * from us_customer_orders, we end up with this larger
query:
SELECT * FROM
(SELECT * FROM
(SELECT * FROM CUSTOMERS c JOIN orders o ON c.id = o.cust_id)
WHERE country_code = "us")That larger query is what MySQL is actually running when it executes select * from
us_customer_orders. Generally MySQL is good at figuring out a performant execution strategy for
such nested views, but all the same rules apply as with any set of complicated subqueries. For
performance, always make sure indexes are available for your views to use at runtime to reduce the
size of intermediate result sets. If we wanted the us_customer_orders query to be faster, we could
put an index on either customers.country_code, or on the columns [customers.id,
customers.country_code], depending on our distribution of data. Doing so will let MySQL produce a
faster query plan in some cases.
Updateable views
Some views can be modified as well as selected from. For example, this query inserts a new customer:
insert into customer_orders
(first_name, last_name, country_code)
values ("zach", "musgrave", "us");This works and is equivalent to inserting these values into the customers table directly. This
will create a new order for the new customer:
insert into customer_orders (cust_id) values (4);This is equivalent to inserting that value into the orders table directly.
This won't work, however:
insert into customer_orders (first_name, last_name, country_code, order_date) values ("zach", "musgrave", "us", now());
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'test.customer_orders'Generally speaking, views can be updated when they are simple SELECT statements onto one or more
base tables, but the exact requirements for a view to be updateable, and what you can expect to
happen when you update one, can be very subtle. We recommend treating views as read-only, and if you
do need to update them, experiment with how this impacts the underlying table data carefully with
test data before rolling it out to production.
What are views used for?
Now that you understand how to define a view in MySQL, you might be asking yourself why you would want to. What are they good for? What do people use these things to do? Views are a general purpose tool you can use to do all sorts of things, but here are some of the more common ones in our experience.
Defining the read operations for your data
For large, complex schemas with many relationships, it may be desirable to define read-only views that clearly document how to access the data usefully. This has several advantages:
- It advertises what operations are available, built into the schema of the database
- It saves the many developers on the team the effort of having to write near-duplicate queries for similar access patterns
- It lets you write a well-optimized query (that makes good use of indexes etc.) that can be re-used in many different applications
Consider a library system with tables for books, holds, and checkouts.
CREATE TABLE patrons(
name varchar(100) primary key,
...
);
CREATE TABLE holds(
isbn varchar(100),
patron_id bigint,
unique key (isbn, patron_id)
);
CREATE TABLE checkouts(
isbn varchar(100),
patron_id bigint,
date date,
KEY (isbn, patron_id)
);We may want to define the logic to summarize how many books a patron has checked out or placed on hold. We can define a view to do this:
CREATE VIEW holds_and_checkouts
(id, num_checkouts, num_holds)
AS SELECT p.id,
(SELECT count(*) FROM checkouts WHERE p.id = patron_id) AS num_checkouts,
(SELECT count(*) FROM holds WHERE p.id = patron_id) AS num_holds
FROM patrons p;Then, anywhere in the application a developer needs to query the number of holds or checkouts a patron has, they can query the view above with an additional filter e.g.:
SELECT * FROM holds_and_checkouts where id = 34098;This is much easier to write whenever it's required, especially for further use as a subquery.
Encapsulating queries for further use
Another common use for defining views is their repeated use a single query. Consider a query on our library books catalog that searches for patrons that have either overdue or lost books, and how many checkouts and holds those patrons have active. We might write this as a union:
SELECT c.patron_id, hc.*
FROM checkouts c JOIN holds_and_checkouts hc ON c.patron_id = hc.id
WHERE c.due_date > now()
UNION DISTINCT
SELECT f.patron_id, hc.*
FROM fines f JOIN holds_and_checkouts hc ON f.patron_id = hc.id
WHERE f.lost_book;Without the view, this query becomes significantly more verbose:
SELECT c.patron_id, hc.*
FROM checkouts c JOIN
(SELECT p.id as id,
(SELECT count(*) FROM checkouts WHERE p.id = patron_id) AS num_checkouts,
(SELECT count(*) FROM holds WHERE p.id = patron_id) AS num_holds
FROM patrons p) AS hc
ON c.patron_id = hc.id
WHERE c.due_date > now()
UNION DISTINCT
SELECT f.patron_id, hc.*
FROM fines f JOIN
(SELECT p.id as id,
(SELECT count(*) FROM checkouts WHERE p.id = patron_id) AS num_checkouts,
(SELECT count(*) FROM holds WHERE p.id = patron_id) AS num_holds
FROM patrons p) AS hc
ON f.patron_id = hc.id
WHERE f.lost_book;As the views and queries they're used in become more complex, the benefit to not repeating the common definition grows. You could also use common table expressions (CTEs) for this use case, but when multiple queries require that same definition, it can make sense to define a view for them to share.
Soft deletes
Soft-deletion
is a schema design pattern where you annotate your database rows with additional information marking
them current or not. Then instead of deleting rows, you UPDATE them to mark them as logically
deleted. When you follow this pattern, each of your application queries must remember to consider
logical deletion to avoid returning logically deleted rows in the normal case. This design pattern
is nice because it allows you to easily examine archival data or reverse deletion events without
heavy-handed interventions like restoring from a backup.
For example, here's how we would create an employees table with soft deletion via an is_active
field.
CREATE TABLE employees(
emp_id bigint primary key,
first_name varchar(100),
last_name varchar(100),
is_active tinyint
);To select all current employees, we would query that table for rows with the is_active flag
set. We can encapsulate that logic as a view:
CREATE VIEW current_employees AS
SELECT * FROM employees WHERE is_active;Then application code that wants to deal with current employees (most use cases) can simply query
the current_employees view as if it were a normal table, rather than having to worry about the
logic of soft deletion on the employees table.
Documenting useful queries for data analytics
Many databases serve double duty: they handle live customer queries and updates during normal business operation (online transaction processing, or OLTP); and then they are queried on some regular schedule by batch jobs for reporting (offline analytic processing, or OLAP). For the latter use case, it can be very useful to define the analytical queries as views. This means the definition of the report lives alongside the data and the schema, and doesn't need to ported to a new place if the reporting framework changes. So let's define our "overdue and lost" reporting query above as a view:
CREATE VIEW overdue_and_lost_holds_and_checkouts AS
SELECT c.patron_id, hc.*
FROM checkouts c JOIN holds_and_checkouts hc ON c.patron_id = hc.id
WHERE c.due_date > now()
UNION DISTINCT
SELECT f.patron_id, hc.*
FROM fines f JOIN holds_and_checkouts hc ON f.patron_id = hc.id
WHERE f.lost_book;Now any reporting framework can run the reporting query by simply calling:
SELECT * FROM overdue_and_lost_holds_and_checkouts;If the definition of the report changes, it can be modified in the single place it lives, and every reporting application that wants to use it doesn't need to change.
Conclusion
This tutorial should get you on your way using views in MySQL. They're pretty handy!
Before you go, did we mention that we built a MySQL-compatible database with built-in version
control called Dolt? Dolt is a great way to experiment with advanced database
features like views, because you have the safety of version control to fall back on -- if you mess
up, you can always dolt reset --hard to roll back to a previous revision. Dolt also lets you diff
two revisions of your database, so you can see what changed when a view is updated, as well as
compare revisions of a view definition to look for bugs, or compare the output of a reporting query
on two different revisions of the data.
Dolt is free and open source, so go check it out! All of the examples in this tutorial work in Dolt's built-in SQL shell, so you can use it to follow along at home.
Have questions about Dolt or MySQL views? Join us on Discord to talk to our engineering team and meet other Dolt users.