Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎T02 Queries‎ > ‎

T02B Nested queries

The subqueries, depending on the expected value and the desired comparison type, can be used in three ways:

  • expr opcompara {ALL|[ANY|SOME]} (orden select)
  • expr [NOT] IN (orden select)
  • [NOT] EXISTS (orden select)

where opcompara is any comparison operator. 

The possible values that a nested select statement returns are :

  • nothing
  • one value (a row and a column),
  • a list of values (various rows and a column).

As long as the subselect returns something, it will be one and only one column (except for the EXISTS operator, explained later).

of one value

expr opcompara ( orden select ) 

We can use the comparison operators to ask if the value of a certain expression is greater, lower, equal, etc than the result of a subquery, as long as this query returns only one rows and one column, this is, only one value.

select descripcion
from asignaturas
where creditos = (select creditos from asignaturas where creditos < 9)

SQL Error: Subquery returns more than 1 row


Although it is also possible to use any query that we are certain of the result, being only one row and one value, the most common thing is using the result of looking for a certain value of a primary key (in general, any combination of columns that does not admit duplicates)


 Subjects that have more credits than the HI subject.

select * from asignaturas
where creditos >(select creditos
from asignaturas
where codigo = 'HI')

codigo descripcion creditos creditosp
DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0
FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5
FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
PC PROGRAMACION CONCURRENTE 6.0 1.5


of a list of values

expr opcompara ALL|[ANY|SOME] (orden select) 

When a result-table contains more than one row (but just one column) we have to use a modifier for the comparison operator.


Subjects with the maximum number of credits

select descripcion
from asignaturas
where creditos >= ALL (select creditos from asignaturas)

descripcion
FUNDAMENTOS DE LA PROGRAMACION
 

The number of credits of the subject must be greater or equal to all the values returned by the subquery (that is the relation of credits of all the subjects). It can be formulated also as

select descripcion
from asignaturas
where creditos (select max(creditos) from asignaturas)



Name of the subjects that are not the ones with less credits.

select descripcion
from asignaturas
where creditos > ANY (select creditos from asignaturas)

descripcion
DISEÑO Y GESTION DE BASES DE DATOS
FUNDAMENTOS DE LAS BASES DE DATOS
FUNDAMENTOS DE LA PROGRAMACION
PROGRAMACION CONCURRENTE

The result will be all the subjects whose credits overcome at least one (any) of the returned values of the subquery. The modifiers some and any are sinonimous. An alternative solution is

select descripcion
from asignaturas
where creditos != (select min(creditos) from asignaturas)



Name of the teachers that teach a subject whose number of credits are not the maximum

select nombre
from profesores p, asignaturas a, imparte i
where p.dni = i.dni and i.asignatura = a.codigo
and creditos < ANY ( select creditos from asignaturas )

nombre
EVA GOMEZ
EVA GOMEZ
RAFAEL ROMERO

Finally, we see that some of these operators are equivalent:
expr IN (orden select) ≈ expr=ANY(orden select)
expr NOT IN (orden select) ≈ expr<>ALL(orden select)
Comments