que puede ser otra SELECT o bien cualquier sentencia de manejo de datos
(INSERT, UPDATE, DELETE). Las subconsultas pueden anidarse unas dentro de
otras tanto como sea necesario (cada SGBD puede tener un nivel máximo de
anidamiento, que difícilmente se alcanzará). En este apartado se muestra cómo
el uso de subconsultas en las cláusulas WHERE y HAVING otorga mayor potencia
para la realización de restricciones. Además, en este apartado se introduce el
uso de subconsultas en la cláusula FROM.
4.7.1. Subconsultas en la cláusula WHERE
La cláusula WHERE se utiliza para realizar restricciones a nivel de filas. El
predicado que se evalúa para realizar una restricción está formado por compa-
raciones unidas por los operadores AND/OR. Cada comparación involucra dos
operandos que pueden ser:
Además de los dos operandos, cada comparación se realiza con un opera-
dor. Hay una serie de operadores que se pueden utilizar con las subconsultas
para establecer predicados en las restricciones. Son los que se muestran a con-
tinuación:
expresión operador ( subconsulta )
En este predicado la subconsulta debe devolver un solo valor (una fila
con una columna). El predicado se evalúa a verdadero si la comparación
indicada por el operador (=, <>, >, <, >=, <=), entre el resultado de
la expresión y el de la subconsulta, es verdadero. Si la subconsulta
devuelve más de un valor (una columna con varias filas o más de una
columna), se produce un error de ejecución.
-- facturas con descuento máximo
SELECT *
FROM
facturas
WHERE
dto = ( SELECT MAX(dto) FROM facturas );
(expr1, expr2, ...) operador ( subconsulta )
En un predicado de este tipo, la subconsulta debe devolver una sola fila
y tantas columnas como las especificadas entre paréntesis a la izquierda
del operador (=, <>, >, <, >=, <=).
Las expresiones de la izquierda expr1, expr2, ... se evalúan y la fila
que forman se compara, utilizando el operador, con la fila que devuelve
la subconsulta.
El predicado se evalúa a verdadero si el resultado de la comparación es
verdadero para la fila devuelta por la subconsulta. En caso contrario, se
evalúa a falso. Si la subconsulta no devuelve ninguna fila, se evalúa a
nulo.2
Dos filas se consideran iguales si los atributos correspondientes son igua-
les y no nulos en ambas; se consideran distintas si algún atributo es
distinto en ambas filas y no nulo. En cualquier otro caso, el resultado del
predicado es nulo.
2Hay que tener en cuenta que una restricción se cumple si el resultado de su predicado
es verdadero; si el predicado es falso o nulo, se considera que la restricción no se cumple.
Si la subconsulta devuelve más de una fila, se produce un error de eje-
cución.
-- facturas con descuento máximo e IVA máximo
SELECT *
FROM
facturas
WHERE
(dto, iva) =
( SELECT MAX(dto), MAX(iva) FROM facturas );
expresión IN ( subconsulta )
El operador IN ya ha sido utilizado anteriormente, especificando una lista
de valores entre paréntesis. Otro modo de especificar esta lista de valores
es incluyendo una subconsulta que devuelva una sola columna. En este
caso, el predicado se evalúa a verdadero si el resultado de la expresión es
igual a alguno de los valores de la columna devuelta por la subconsulta.
El predicado se evalúa a falso si no se encuentra ningún valor en la
subconsulta que sea igual a la expresión; cuando la subconsulta no
devuelve ninguna fila, también se evalúa a falso.
Si el resultado de la expresión es un nulo, o ninguno de los valores de la
subconsulta es igual a la expresión y la subconsulta ha devuelto algún
nulo, el predicado se evalúa a nulo.
-- pueblos en donde hay algún cliente
SELECT codpue, nombre
FROM
pueblos
WHERE
codpue IN ( SELECT codpue FROM clientes);
(expr1, expr2, ...) IN ( subconsulta )
En este predicado la subconsulta debe devolver tantas columnas como
las especificadas entre paréntesis a la izquierda del operador IN.
Las expresiones de la izquierda expr1, expr2, ... se evalúan y la fila
que forman se compara con las filas de la subconsulta, una a una.
El predicado se evalúa a verdadero si se encuentra alguna fila igual en la
subconsulta. En caso contrario se evalúa a falso (incluso si la subconsulta
no devuelve ninguna fila).
Dos filas se consideran iguales si los atributos correspondientes son igua-
les y no nulos en ambas; se consideran distintas si algún atributo es
distinto en ambas filas y no nulo. En cualquier otro caso, el resultado del
predicado es nulo.
Si la subconsulta devuelve alguna fila de nulos y el resto de las filas son
distintas de la fila de la izquierda del operador IN, el predicado se evalúa
a nulo.
-- clientes que han comprado en algún mes en
-- que ha comprado el cliente con código 282
SELECT DISTINCT codcli
FROM
facturas
WHERE
( EXTRACT(month FROM fecha),
EXTRACT(year FROM fecha) )
IN ( SELECT EXTRACT(month FROM fecha),
EXTRACT(year FROM fecha)
FROM
facturas
WHERE
codcli = 282);
expresión NOT IN ( subconsulta )
Cuando IN va negado, el predicado se evalúa a verdadero si la expresión
es distinta de todos los valores de la columna devuelta por la subconsulta.
También se evalúa a verdadero cuando la subconsulta no devuelve nin-
guna fila. Si se encuentra algún valor igual a la expresión, se evalúa a
falso.
Si el resultado de la expresión es un nulo, o si la subconsulta devuelve
algún nulo y valores distintos a la expresión, el predicado se evalúa a
nulo.
-- número de clientes que no tienen facturas
SELECT COUNT(*)
FROM
clientes
WHERE
codcli NOT IN ( SELECT codcli
FROM
facturas
WHERE
codcli IS NOT NULL );
Nótese que en el ejemplo se ha incluido la restricción codcli IS NOT
NULL en la subconsulta porque la columna FACTURAS.codcli acepta nu-
los. Un nulo en esta columna haría que el predicado NOT IN se evaluara
a nulo para todos los clientes de la consulta principal.
(expr1, expr2, ...) NOT IN ( subconsulta )
En este predicado, la subconsulta debe devolver tantas columnas como
las especificadas entre paréntesis a la izquierda del operador NOT IN. Las
expresiones de la izquierda expr1, expr2, ... se evalúan y la fila que
forman se compara con las filas de la subconsulta, fila a fila.
El predicado se evalúa a verdadero si no se encuentra ninguna fila igual
en la subconsulta. También se evalúa a verdadero si la subconsulta no
devuelve ninguna fila. Si se encuentra alguna fila igual, se evalúa a falso.
Dos filas se consideran iguales si los atributos correspondientes son igua-
les y no nulos en ambas; se consideran distintas si algún atributo es
distinto en ambas filas y no nulo. En cualquier otro caso, el resultado del
predicado es nulo.
Si la subconsulta devuelve alguna fila de nulos y el resto de las filas son
distintas de la fila de la izquierda del operador NOT IN, el predicado se
evalúa a nulo.
-- clientes que no tienen facturas con IVA y dto
-- como tienen los clientes del rango especificado
SELECT DISTINCT codcli
FROM
facturas
WHERE
( COALESCE(iva,0), COALESCE(dto,0) )
NOT IN ( SELECT COALESCE(iva,0), COALESCE(dto,0)
FROM
facturas
WHERE
codcli BETWEEN 171 AND 174);
expresión operador ANY ( subconsulta )
En este uso de ANY la subconsulta debe devolver una sola columna. El
operador es una comparación (=, <>, >, <, >=, <=).
El predicado se evalúa a verdadero si la comparación establecida por el
operador es verdadera para alguno de los valores de la columna devuelta
por la subconsulta. En caso contrario se evalúa a falso.
-- facturas con IVA como los de las facturas sin dto
SELECT *
FROM
facturas
WHERE
iva = ANY( SELECT iva
FROM facturas
WHERE COALESCE(dto,0) = 0 );
Si la subconsulta no devuelve ninguna fila, devuelve falso. Si ninguno de
los valores de la subconsulta coincide con la expresión de la izquierda del
operador y en la subconsulta se ha devuelto algún nulo, se evalúa a nulo.
En lugar de ANY puede aparecer SOME, son sinónimos. El operador IN es
equivalente a = ANY.
(expr1, expr2, ...) operador ANY ( subconsulta )
En este uso de ANY la subconsulta debe devolver tantas columnas como las
especificadas entre paréntesis a la izquierda del operador. Las expresiones
de la izquierda expr1, expr2, ... se evalúan y la fila que forman se
compara con las filas de la subconsulta, fila a fila.
El predicado se evalúa a verdadero si la comparación establecida por el
operador es verdadera para alguna de las filas devueltas por la subcon-
sulta. En caso contrario se evalúa a falso (incluso si la subconsulta no
devuelve ninguna fila).
Dos filas se consideran iguales si los atributos correspondientes son igua-
les y no nulos en ambas; se consideran distintas si algún atributo es
distinto en ambas filas y no nulo. En cualquier otro caso, el resultado del
predicado es nulo.
Si la subconsulta devuelve alguna fila de nulos, el predicado no podrá ser
falso (será verdadero o nulo).
-- clientes que han comprado algún mes
-- en que ha comprado el cliente especificado
SELECT DISTINCT codcli
FROM
facturas
WHERE
( EXTRACT(month FROM fecha),
EXTRACT(year FROM fecha) )
= ANY( SELECT EXTRACT(month FROM fecha),
EXTRACT(year FROM fecha)
FROM
facturas
WHERE
codcli = 282);
En lugar de ANY puede aparecer SOME.
expresión operador ALL ( subconsulta )
En este uso de ALL la subconsulta debe devolver una sola columna. El
operador es una comparación (=, <>, >, <, >=, <=).
El predicado se evalúa a verdadero si la comparación establecida por
el operador es verdadera para todos los valores de la columna devuelta
por la subconsulta. También se evalúa a verdadero cuando la subcon-
sulta no devuelve ninguna fila. En caso contrario se evalúa a falso. Si la
subconsulta devuelve algún nulo, el predicado se evalúa a nulo
-- facturas con descuento máximo
SELECT *
FROM
facturas
WHERE
dto >= ALL ( SELECT COALESCE(dto,0)
FROM facturas );
Nótese que, si en el ejemplo anterior, la subconsulta no utiliza COALESCE
para convertir los descuentos nulos en descuentos cero, la consulta prin-
cipal no devuelve ninguna fila porque al haber nulos en el resultado de
la subconsulta, el predicado se evalúa a nulo.
El operador NOT IN es equivalente a <>ALL.
(expr1, expr2, ...) operador ALL ( subconsulta )
En este uso de ALL, la subconsulta debe devolver tantas columnas como
las especificadas entre paréntesis a la izquierda del operador.
Las expresiones de la izquierda expr1, expr2, ... se evalúan y la fila
que forman se compara con las filas de la subconsulta, fila a fila.
El predicado se evalúa a verdadero si la comparación establecida por el
operador es verdadera para todas las filas devueltas por la subconsul-
ta; cuando la subconsulta no devuelve ninguna fila también se evalúa a
verdadero. En caso contrario se evalúa a falso.
Dos filas se consideran iguales si los atributos correspondientes son igua-
les y no nulos en ambas; se consideran distintas si algún atributo es
distinto en ambas filas y no nulo. En cualquier otro caso, el resultado del
predicado es nulo.
Si la subconsulta devuelve alguna fila de nulos, el predicado no podrá ser
verdadero (será falso o nulo).
-- muestra los datos del cliente especificado si
-- siempre ha comprado sin descuento y con 18% de IVA
SELECT *
FROM
clientes
WHERE
codcli = 162
AND
( 18, 0 ) =
ALL (SELECT COALESCE(iva,0), COALESCE(dto,0)
FROM
facturas
WHERE
codcli = 162 );
Cuando se utilizan subconsultas en predicados, el SGBD no obtiene el
resultado completo de la subconsulta, a menos que sea necesario. Lo que hace
es ir obteniendo filas de la subconsulta hasta que es capaz de determinar si el
predicado es verdadero.
4.7.2. Subconsultas en la cláusula HAVING
La cláusula HAVING permite hacer restricciones sobre grupos y necesaria-
mente va precedida de una cláusula GROUP BY. Para hacer este tipo de restric-
ciones también es posible incluir subconsultas cuando sea necesario.
La siguiente consulta obtiene el código del pueblo que tiene más clientes:
SELECT codpue
FROM
clientes
GROUP BY codpue
HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
FROM
clientes
GROUP BY codpue );
En primer lugar se ejecuta la subconsulta, obteniéndose una columna de nú-
meros en donde cada uno indica el número de clientes en cada pueblo. La
subconsulta se sustituye entonces por los valores de esta columna, por ejem-
plo:
SELECT codpue
FROM
clientes
GROUP BY codpue
HAVING COUNT(*) >= ALL (1,4,7,9,10);
Por último, se ejecuta la consulta principal. Para cada grupo se cuenta el
número de clientes que tiene. Pasan la restricción del HAVING aquel o aquellos
pueblos que en esa cuenta tienen el máximo valor.
4.7.3. Subconsultas en la cláusula FROM
También es posible incluir subconsultas en la cláusula FROM, aunque en este
caso no se utilizan para construir predicados sino para realizar una consulta
sobre la tabla que se obtiene como resultado de ejecutar otra consulta. Siempre
que se utilice una subconsulta en el FROM se debe dar un nombre a la tabla
resultado mediante la cláusula AS.
SELECT COUNT(*), MAX(ivat), MAX(dtot)
FROM
( SELECT DISTINCT COALESCE(iva,0) AS ivat,
COALESCE(dto,0) AS dtot
FROM
facturas ) AS t;
La consulta anterior cuenta las distintas combinaciones de IVA y descuento y
muestra el valor máximo de éstos. Nótese que se han renombrado las colum-
nas de la subconsulta para poder referenciarlas en la consulta principal. Esta
consulta no se puede resolver si no es de este modo ya que COUNT no acepta
una lista de columnas como argumento.
4.7.4. Ejemplos
Ejemplo 4.9 Se quiere obtener los datos completos del cliente al que pertenece
la factura 5886.
Para dar la respuesta podemos hacerlo en dos pasos, es decir, con dos consultas
separadas:
SELECT codcli FROM facturas WHERE codfac = 5886;
codcli
--------
264
SELECT *
FROM
clientes
WHERE
codcli = 264;
Puesto que es posible anidar las sentencias SELECT para obtener el resultado
con una sola consulta, una solución que obtiene el resultado en un solo paso
es la siguiente:
SELECT *
FROM
clientes
WHERE
codcli = ( SELECT codcli
FROM facturas WHERE codfac = 5886 );
Se ha utilizado el operador de comparación = porque se sabe con certeza
que la subconsulta devuelve un solo código de cliente, ya que la condición de
búsqueda es de igualdad sobre la clave primaria de la tabla del FROM.
Ejemplo 4.10 Se quiere obtener los datos completos de los clientes que tienen
facturas en agosto del año pasado. El resultado se debe mostrar ordenado por
el nombre del cliente.
De nuevo se puede dar la respuesta en dos pasos:
SELECT codcli FROM facturas
WHERE EXTRACT(month FROM fecha)=8
AND
EXTRACT(year FROM fecha) =
EXTRACT(year FROM CURRENT_DATE)-1;
codcli
--------
105
12
.
.
.
342
309
357
SELECT *
FROM
clientes
WHERE
codcli IN (105,12,...,342,309,357);
Se ha utilizado el operador IN porque la primera consulta devuelve varias
filas. Esto debe saberse sin necesidad de probar la sentencia. Como esta vez
no se seleccionan las facturas por una columna única (clave primaria o clave
alternativa), es posible que se obtengan varias filas y por lo tanto se debe
utilizar IN.
Tal y como se ha hecho en el ejemplo anterior, ambas sentencias pueden
integrarse en una sola:
SELECT *
FROM
clientes
WHERE
codcli IN ( SELECT codcli FROM facturas
WHERE EXTRACT(month FROM fecha)=8
AND
EXTRACT(year FROM fecha) =
EXTRACT(year FROM CURRENT_DATE)-1 )
ORDER BY nombre;
4.7.5. Algunas cuestiones importantes
A continuación se plantean algunas cuestiones que es importante tener en
cuenta cuando se realizan subconsultas:
Las subconsultas utilizadas en predicados del tipo expresión operador
(subconsulta) o (expr1, expr2, ...) operador (subconsulta) de-
ben devolver siempre una sola fila; en otro caso, se producirá un error. Si
la subconsulta ha de devolver varias filas se debe utilizar IN, NOT IN,
operador ANY, operador ALL.
Es importante ser cuidadosos con las subconsultas que pueden devolver
nulos. Una restricción se supera si el predicado se evalúa a verdadero; no
se supera si se evalúa a falso o a nulo. Dos casos que no conviene olvidar
son los siguientes:
• NOT IN se evalúa a verdadero cuando la subconsulta no devuelve
ninguna fila; si la subconsulta devuelve un nulo/fila de nulos, se
evalúa a nulo.
• operador ALL se evalúa a verdadero cuando la subconsulta no de-
vuelve ninguna fila; si la subconsulta devuelve un nulo/fila de nulos,
se evalúa a nulo.
Cuando se utilizan subconsultas en la cláusula FROM es preciso renombrar
las columnas del SELECT de la subconsulta que son expresiones. De ese
modo, será posible hacerles referencia en la consulta principal. Además,
la tabla resultado de la subconsulta también se debe renombrar en el
FROM de la consulta principal.
4.8. Consultas multitabla
No hay comentarios:
Publicar un comentario