Views in SQL: Underutilised, Yet Very Useful

A view is a virtual table in SQL that functions similarly to a standard table, but does not need to be physically stored, i.e. it is only stored in memory and does not take up actual storage space.

For instance, there will often be times when one would like to see a subset of data from a table, or data organised in a certain way.

However, if this is simply to facilitate understanding of the data — then storing the same in a table can be quite inefficient — as this means that extra storage space will be used to effectively create duplicated data from another table.

Let’s take a look at some examples.

Creating a view with ORDER BY and LIMIT queries

Consider the following hypothetical table called clothes (all entries made up by the author) describing a range of clothes items for sale at a store.

item               | price   | size 
-------------------+---------+--------
Sky Blue Jeans     |  79.99  | 31
Fire Red Jeans     |  89.99  | 36
Sky Blue Shirt     |  59.99  | 38
Grass Green Shirt  |  69.99  | 34
...
Peach Purple Hat   |  79.99  | 40
Sun Yellow Jeans   |  109.99 | 42
Olive Green Hat    |  89.99  | 37

Imagine there are thousands of these entries in a table. However, the owner of the store would like to create a view whereby 1) only the item and price is displayed, and 2) only the top three entries by price are displayed. Let’s create a view and call it condensedclothes:

create view condensedclothes as select item, price from clothes order by price desc limit 3;

The resulting output is as follows:

>>> select view condensedclothes from database;
item               | price   
-------------------+---------
Black Sunglasses   |  149.99
Blue Sneakers      |  129.99
Red Polo Shirt     |  129.99

In this hypothetical scenario, we can see that the following view gives the owner a quick view at which items are retailing at the highest price. However, using a view has advantages in that 1) it allows the owner to view the relevant information quickly without having to input a separate query every time and 2) the view does not use storage space to display since it is stored in memory.

Creating a view with INNER JOIN

In addition to the table above, assume that there also exists another table which provides descriptions of the listed items:

>>> select * from clothes limit 1;
item               | price   | size 
-------------------+---------+--------
Sky Blue Jeans     |  79.99  | 31

>>> select * from clothes2 limit 1;
item                | colour   | type 
--------------------+----------+----------
Grass Green T-Shirt | Green    | T-Shirt

Now, let’s assume that the owner wants to join the two tables together and view the top three selling items by price — but this time with the added information from the clothes2 table.

>>> create view orderedclothes as select t1.item, t1.price, t2.colour, t2.type from clothes as t1 inner join clothes2 as t2 on t1.item=t2.item order by t1.price desc limit 3;
CREATE VIEW

>>> select * from orderedclothes;
           item           | price   | colour  | type
--------------------------+---------+---------+-------
Black Sunglasses          |  149.99 | Black   | Sunglasses
Blue Sneakers             |  129.99 | Blue    | Sneakers
Red Polo Shirt            |  129.99 | Red     | Polo Shirt
(3 rows)

As we can see, selecting the newly created view (named orderedclothes in this case), allows us to see the top three items by descending price, but with the added information from the second table.

Again, the view is stored in memory and no storage from the database itself is being used.

Disadvantages of views

As we have seen, views are quite useful from the perspective of being able to view important parts of a table quickly and with ease. They also have the advantage of not taking up storage space.

However, using views can have some disadvantages — mainly to do with manipulation of the data.

For instance, while updating the data contained in a view is possible — it cannot be done in a range of circumstances. This includes the use of joins in updating the view, use of a GROUP BY clause, along with the DISTINCT clause being unavailable when using views.

In this regard, views are not always the best option if one needs to update the data in that view regularly.

However, it is worth remembering that the data in the view itself can be stored as a separate table.

For instance, let us say that we wished to take the condensedclothes view and store the data in a table (we will call the table condensedtable). We can do so as follows:

>>> create table condensedtable as select * from condensedclothes;
SELECT 3

>>> select * from condensedtable;
item               | price   
-------------------+---------
Black Sunglasses   |  149.99
Blue Sneakers      |  129.99
Red Polo Shirt     |  129.99
(3 rows)

Once data from a view has now been stored in a table, the user has much more flexibility in terms of being able to utilise a wider range of queries that one would not be able to when working with the view itself.

Conclusion

This article has been an introduction to views in SQL and how they can be used. Particularly, we took a look at:

  • The advantages of views and reasons for using them
  • How to implement views across a range of SQL queries
  • Disadvantages of views and when it makes sense to store in table format instead

Again, one of the major advantages of using views is that it allows for viewing a condensed version of a table without having to use storage space — as the view is stored in memory.

Indeed, views can offer the best of both worlds in terms of being able to 1) analyse data quickly in a view and 2) store the data in a table when wishing to carry out more advanced queries.

Many thanks for reading!

References

  • Stephens, Jones and Plew (2016): SamsTechYourself SQL in 24 Hours