ALL YOU WANT TO KNOW ABOUT VIEWS

WHAT IS A VIEW?

In a short sentence, a view is a “saved query” associated with a name. Once you create the view (or save the query), you can use it as a table in other queries. Trying to explain with more detail what a view is, we can say that a view is another type of database objects like tables, indexes or stored procedures. From the point of view of a developer who is trying to write a SELECT query, a view is like any other regular table. The view can be used in the FROM clause as any other table, and view columns can be referenced like any other column table in the SELECT, WHERE, GROUP BY or any other place of the query where a table column is valid.

However, Views have an essential difference related to tables. While tables store records physically on disk, views don’t store records on any place. The records are ever stored on one or more tables, and those tables are called “base tables”. In other cases ( as we will see in this article) the records can be calculated “on the fly”, but at the end we ever have a base table providing physical records. Let’s see an example.

AN EXAMPLE VIEW

In this article, we will use the following tables World Athletic Games and Results, with a few records.

 

We will start by creating a simple sample view to show the idea behind a view. We will create a view called world_wide_records_breaks(city, year, discipline, athlete) to have a simple way to obtain when a new record was achieved for any specific discipline. The view creation SQL statement is:

As we see in the previous SQL statement, a view is a simple association between a view name and a SELECT statement. Then every time the view is referenced, the associated SELECT is executed to obtain the records of the view. However is important to note that the view records are not stored anywhere, they are obtained “on the fly” by executing the view associated SELECT.

Other important property of the previous view, is the JOIN clause. As we can see, the last view is based on two base tables: world_athletics_games and results.

Once we created the view, we can use it as a regular table in any SELECT statement. Let’s see the following example where we use the just created view to obtain the athletes’ names who break the Worldwide record in Walk race 50K Women discipline.

CALCULATED FIELDS IN A VIEW

Let’s suppose now, that we need to create different reports related to how many medals won every country. The tables Results and athletic_world_games have all the data we need, however, the schemas of these tables are not the best for the type of queries we are going to execute. Then it is better to create a view, based on tables Results and athletic_world_games .

The view name will be country_medals, and the schema of the view will be:

country_medals(country, gold_medals, silver_medals, bronze_medals, year)

In the previous view, three columns are the result of a calculation: gold_medals, silver_medals and bronze_medals. When we create views with calculated columns, we must define the names of this columns by using the sub-clause “as” in the creation of the view

Now, we are going to consume our new view country_medals. Let’s think in a query to obtain those countries which have two or more gold and silver medals combined. Let’s see the query:

VIEWS OVER VIEWS

Why not use a view, to create another view? It is perfectly valid to do that. Let’s see an example. Suppose we need a ranking of countries. The position of a country in the ranking is calculated based on the number of medals won by the country. Naturally, each type of medals has a different weight. Gold medals give 5 points, silver 3 points, bronze 1 point.

Then if we plan to work and execute many queries over this ranking, the best idea is to create a view to obtain the ranking, then we can perform other queries over the view. Let’s create the view ranking using the previous view country_medals as the “base table”.

Then we can use the view ranking to obtain the complete classification

The reader should note that ranking is a view created over the view country_medals.

CONCLUSION

As we see in this article, views are a compelling database object. We didn’t mention previously, but views allow us to introduce a sort of data redundancy in a controlled way into a database. In this article, we created views with calculated fields, why not to see these fields as redundant fields?. Another point to mention is about we only consumed views by executing SELECTs.