SQL

Course 20: Unions & Views in SQL

169 Views
Oct 3, 2021
First Example Creation Table in SQL

5. Unions

Unions are set operations, and consist in accumulating the results of several selectin the same result table. There are four union operators:

  • union : combines rows from several tables, eliminating duplicates;
  • union all : performs the same meeting, without eliminating duplicates;
  • minus : takes the results of the first table and subtracts those of the second;
  • intersect : only takes the results that are in the two tables.

Note that only unionand union allare supported in MySQL.

Let’s see an example of use.

Example 69. Syntax of union

clause_select
    UNION [ALL]
   clause_select 
[...]

A view can be defined as a virtual table, built from a query that can be arbitrarily complex. Formally, a view is a way of storing a query so that you can easily refer to it at any time. A user who would need these results can therefore launch a query directly on the view, as if it were a table. When a table on which a view is built is modified, then the view “takes into account” this modification for subsequent queries that would be made on it.

Views are a convenient way to present data in a simple way, showing only what the user needs to see, through simple queries for them. The real structure of the data is hidden, and the sometimes complex queries to access it too. Beyond this convenient way of doing things, as has already been said, it also makes it possible to control the data from the point of view of security and access rights.

6.1. Creating a view

The syntax for creating a view is as follows.

Example 70. Creating a view

CREATE VIEW nom_de_la_vue [(liste_de_colonnes)]
     AS expression_select


The expression selectis in fact a classic selection, as we have already written.

The column list takes the columns from the query result and assigns them the names from the list. This list must therefore contain as many elements as there are columns in the query result.

Generally speaking, a view is a table that can only be read. There are actually a few cases in which you can insert or modify the elements of a view. These cases are defined very precisely in the documentations of the server that we are using.

A view is then used exactly like a table. It can be used as an argument of a select. Each time a query is made on a view, the queries that were used to create the view are executed.

The command that allows you to delete a view is as follows.

Example 71. Deleting a view

DROP VIEW view_name;


No data is erased when deleting a view, unlike deleting a table.

There are six types of view:

  • The projections of a single table in a view. This system is useful for restricting access to certain rows or columns: it is indeed possible to prohibit a user from reading a table, and to authorize him to read a view defined on part of this table.
  • Calculated columns: the view has a column that is the sum of others. This type of view is useful for creating reports or summaries, and highlighting certain things.
  • Translated columns: the view has a join that allows you to change a numeric reference into a readable name. In the example of our table Marins, such a view would present the name of the sailor and his town of birth. In this case the municipality identifier of the table Marinsserves as a join, and is hidden from the user.
  • Grouped views: the view has a column that contains an operation on several rows of the original table. There is therefore a clause group byand an aggregation function in the selection criterion.
  • Views combining multiple tables: use a union in the selection clause.
  • Views that build on other views. It is perfectly possible to create a view from one or more other views. You just have to be careful not to create a circular reference when creating such things.

6.2. Examples of views

6.2.1. Attached view

Let’s create a view v_Marinsthat gives us the name of the town of birth of our sailors directly.

Example 72. Joined view

create view v_Marins (name, firstname, birth, ddmort, municipality)
     as  
       select Marin.name, firstname, birth, ddmort, Communes.name
        from Marins
        join Communes   on Marins.id_commune = Communes.id;


This view can be used exactly like a table, for example by executing:

Example 73. Selection on a view

select *   from v_Marins;


Note that for a user new to SQL, it is much easier to query a view than two joined tables.

Any modification made to one of the tables Marinsor Communesis of course automatically propagated in the view.

6.2.2. Grouped view

We can also create a view from the query which allowed us to obtain the tonnage delivered by each boat.

Example 74. Grouped view – 1

create view v_tonnage
     as 
       select boat_name, sum (tonnage)   as tonnage
        from Boats   left   outer   join Deliveries
        on (Boats.id = Deliveries.boat_id)
        group   by Boats.boat_name;


We saw that for Amethyst, the tonnage was zero, which can be a problem. We can also write another view, which is based on this one and which handles the case where the tonnage is zero.

Example 75. Grouped view – 2

create view v_tonnage2 (vessel_name, tonnage)
     as 
       select vessel_name, tonnage   from v_tonnage   where tonnage   is   not   null 
       union 
       select vessel_name, 0   from v_tonnage   where tonnage   is   null ;


We can see that the zero value for the tonnage carried by the Amethyst is now equal to 0, which is a better way to put it.

We could of course have mixed the definitions of the two views into one.

Leave a Reply

Your email address will not be published. Required fields are marked *