Embedded Files
Having
Having
Once the calculated information over the groups of rows is obtained using the group by, it is possible to filter these results and showing only those we need using the having clause. Having is to group by the same as where is to select-from.
Likewise the where clause is used to select rows, the having clause is used to select groups; if in the where the condition specified affects to the tuples of the whole table, the group by perfoms calculus over this previous selection and returns as a result a table with all the calculated information for each group of that selection. Over this last, the having clause will eliminate those tuples that do not fulfull the condition.
Where filters the data stored in a table and having the calculated information.
We can understand the process of a query with group by-having as a previous execution of the select-from-where part and, from this intermediate result, we obtain the calculated information with group by and, finally, the filtering with having.
How many subjects teach each teacher?
select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni = i.dni
group by p.dni, nombre
How many subjects are taught by teachers with 2 or more subjects?
select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni = i.dni
group by p.dni, nombre
having count(*) >= 2
Without considering the FBD subject, how many subjects are taught by teachers with 2 or more subjects?
select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni = i.dni
and asignatura != 'FBD'
group by p.dni, nombre
having count(*) >= 2
Step by step, the previous query:
where has eliminated the tuples of imparte with subject's code as FBD.
group by calculates, for each teacher, the number of subjects he/she teaches. Due to the fact that FBD is not included, Eva Gómez only teach, as wll as Rafael Romero, one only subject.
having eliminates from the result of the previous step all those tuples with a value in the count of the rows less than 2. The result is empty due to the fact that no rows fulfill the condition.
Found 0 rows
The condition of the having clause is not necessarily over the calculus to show, as it can be seen in the next example.
How many subjects teach every teacher in the case that the sum of the credits are greater than 5?
select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i, asignaturas a
where p.dni = i.dni and a.codigo = i.asignatura
group by p.dni, nombre
having sum(creditos) > 5
MySQL allows to move away from SQL standard
MySQL allows to move away from SQL standard
Our server set is @@SQL_MODE = 'ONLY_FULL_GROUP_BY'.
We have to be careful with a feature of MySQL that also depends on the configuration of the server. The issue is that it is possible that MySQL accepts the following order in which we have omitted the column "nombre" from the group by:
DNI and name of the coordinator and how many subjects he/she coordinates
set @modo = @@SQL_MODE; -- para poder restaurar la configuración al final
set sql_mode = '';
select dni, nombre, count(*) coordina
from asignaturas a, coordinadores c
where c.asig = a.codigo
group by dni;
set SQL_MODE = @modo; -- restaurada configuración
In this case, there is no problem because in the case that for each dni there is just one name, dni is the identifier of the coordinators, but this is not always the case. As in many other things, MySQL offers its own functionalities that can be chosen or not, but moving away from the standard makes more difficult the migration to others DBMS. MySQL leaves in hands of the one doing the query taking care of the criteria for the grouping, making sure that the omitted columns would not alter the result. There are authors in favour to "move away" from the standard, like Roland Bouman.
Another feature of MySQL is that allows using having by default without group by:
set @modo = @@SQL_MODE; -- para poder restaurar la configuración al final
set sql_mode = '';
select * from asignaturas
having creditos > 3;
set SQL_MODE = @modo; -- restaurada configuración
The idea of this "permissiveness" is that where does not allow to use agregation functions whereas having does. Again, this MySQL extension is not accepted by all the DBMS.
In any case, we do not recommend these ways of querying, or at least not in a basic learning context, neither will be accepted in our evaluated exercises.
Page updated
Google Sites
Report abuse