Views

When designing your database, you may need to use the same subset of data many times. However, it often means creating a rather complex query over and over. In this topic, we will find out how the view can make your life easier.

What are views?

Quite simply, the view is a mechanism for querying data. It is saved in the database as a named select statement and is since available for repeated use. Unlike tables, views do not involve data storage. Sounds great, right?

For instance, you want to create a view for storing your favorite books. Below you can see the definition of this view. Only rows with the plot_score value equal to 5 get there.

CREATE VIEW favorite_books AS
SELECT 
    author_name, 
    book_name
FROM 
    books
WHERE 
    plot_score = 5;

And here is the general case of the view definition:

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name AS
SELECT 
    column1, 
    column2, 
    ...
FROM 
    table_name
WHERE 
    condition;

Let's take a closer look at the syntax:

• OR REPLACE — thanks to this optional construction, if a view with this name exists, the old one will be deleted, and a new one will be created instead.

• ALGORITHM — defines the algorithm used when accessing the view. UNDEFINED algorithm lets MySQL choose the best one for the situation, it is a default value. The TEMPTABLE algorithm puts the contents of the view in a temporary table, to which queries are made. In the case of the MERGE algorithm, MySQL appends the definition part of the view to the query, that mentioned the view, and the big, resulting query is executed. MERGE is more efficient, but it can't always be used. You can read about it here.

You also can use JOIN in your views.

Why else use views?

With views, you can keep some data private. Look: you design a database for an online shop. There is a table 'customers' with their names, email, physical addresses, favorite products, and so on. An ad producer wants to create a newsletter, so you give him access to 'customers'. However, the company's privacy policy forbids you to do that, as some parts of the data (like physical addresses) have to be secure. In order not to change the original database structure and not to get a reprimand from the security department, you can use view: just create one called 'customers_ad' with free distribution data. And that's it!

Also, it may be very useful to conditional joining partitioned data if you break some large tables into smaller pieces. Or to aggregate some data, for example, to make a monthly report. In the case below, we've created a view, showing the total sales for each film category. Then you can work with this selection and decide, for instance, what new films you want to add to your 'inventory'.

CREATE VIEW sales_by_film_category
AS
SELECT
   c.name AS category,
   SUM(p.amount) AS total_sales
FROM payment AS p
   JOIN rental AS r ON p.rental_id = r.rental_id
   JOIN inventory AS i ON r.inventory_id = i.inventory_id
   JOIN film AS f ON i.film_id = f.film_id
   JOIN film_category AS fc ON f.film_id = fc.film_id
   JOIN category AS c ON fc.category_id = c.category_id
GROUP BY c.name;

Updatable and insertable views

For better understanding let's imagine a view as a window to the 'main' table. So, updating data in the view, you actually update it in the 'main', as if you work with data through a window or a hole.

Unfortunately (or luckily) there are a lot of restrictions to creating an updatable and insertable view:

• No aggregate functions are used

• The view does not have GROUP BY

• The FROM uses only INNER JOIN

• There're no subqueries in the SELECT or from FROM clause

And that's not all! You can read more about it in the official documentation.

Summary

So, in this topic you've learned that the view is another data presentation method, that is saved in the database as a named select statement and, unlike tables, does not physically store data. You can apply it to save time when using complex queries, to keep some data private, to join or aggregate some data.

Now you have a great opportunity to make your database structure more elegant and your code more simple by using this tool!