Consultes avançades - Subconsultes de diverses files
-
Quan les subconsultes tornen més d'una fila no podem utilitzar els mateixos operadors de les subconsultes d'una sola fila.
-
No podem comparar un valor amb una sèrie de valors, per tant, necessitem nous operadors.
-
Els operadors de subconsultes de diverses files són:
-
IN -
ANY -
ALL
-
-
Tots aquests operadors poden utilitzar-se amb el
NOT
IN
L'operador IN s'utilitza quan volem seleccionar les files, la columna de les quals està a la llista que ens retorna la subconsulta.
Com a exemple: Empleats contractats el mateix any que algun treballador del departament 80.
SELECT last_name, job_id, department_id, year(hire_date)
FROM employees
WHERE YEAR(hire_date) IN (SELECT YEAR(hire_date)
FROM employees
WHERE department_id = 80);
+-----------+----------+---------------+-----------------+
| last_name | job_id | department_id | year(hire_date) |
+-----------+----------+---------------+-----------------+
| Matos | ST_CLERK | 50 | 1998 |
| Vargas | ST_CLERK | 50 | 1998 |
| Zlotkey | SA_MAN | 80 | 2000 |
| Abel | SA_REP | 80 | 1996 |
| Taylor | SA_REP | 80 | 1998 |
| Hartstein | MK_MAN | 20 | 1996 |
+-----------+----------+---------------+-----------------+
ANY
L'operador ANY s'utilitza quan volem seleccionar les files, la columna de les quals compleix la condició de >, >=, <, <=, <> d'almenys un valor de la llista.
Com a exemple: Empleats contractats abans que algun treballador del departament 80.
SELECT last_name, job_id, department_id, year(hire_date)
FROM employees
WHERE hire_date < ANY (SELECT hire_date
FROM employees
WHERE department_id = 80);
+-----------+------------+---------------+-----------------+
| last_name | job_id | department_id | year(hire_date) |
+-----------+------------+---------------+-----------------+
| King | AD_PRES | 90 | 1987 |
| Kochhar | AD_VP | 90 | 1989 |
| De Haan | AD_VP | 90 | 1993 |
| Hunold | IT_PROG | 60 | 1990 |
| Ernst | IT_PROG | 60 | 1991 |
| Lorentz | IT_PROG | 60 | 1999 |
| Mourgos | ST_MAN | 50 | 1999 |
| Rajs | ST_CLERK | 50 | 1995 |
| Davies | ST_CLERK | 50 | 1997 |
| Matos | ST_CLERK | 50 | 1998 |
| Vargas | ST_CLERK | 50 | 1998 |
| Abel | SA_REP | 80 | 1996 |
| Taylor | SA_REP | 80 | 1998 |
| Grant | SA_REP | NULL | 1999 |
| Whalen | AD_ASST | 10 | 1987 |
| Hartstein | MK_MAN | 20 | 1996 |
| Fay | MK_REP | 20 | 1997 |
| Higgins | AC_MGR | 110 | 1994 |
| Gietz | AC_ACCOUNT | 110 | 1994 |
+-----------+------------+---------------+-----------------+
ALL
L’operador ALL s’utilitza quan volem seleccionar les files, la columna de les quals compleix la condició de >, >=, <, <=, <> de tots els valors de la llista.
Com a exemple: Empleats contractats abans que tots els treballadors del dept 80.
SELECT last_name, job_id, department_id, year(hire_date)
FROM employees
WHERE hire_date < ALL (SELECT hire_date
FROM employees
WHERE department_id = 80);
+-----------+------------+---------------+-----------------+
| last_name | job_id | department_id | year(hire_date) |
+-----------+------------+---------------+-----------------+
| King | AD_PRES | 90 | 1987 |
| Kochhar | AD_VP | 90 | 1989 |
| De Haan | AD_VP | 90 | 1993 |
| Hunold | IT_PROG | 60 | 1990 |
| Ernst | IT_PROG | 60 | 1991 |
| Rajs | ST_CLERK | 50 | 1995 |
| Whalen | AD_ASST | 10 | 1987 |
| Hartstein | MK_MAN | 20 | 1996 |
| Higgins | AC_MGR | 110 | 1994 |
| Gietz | AC_ACCOUNT | 110 | 1994 |
+-----------+------------+---------------+-----------------+
Valors NULL
COMPTE, si la subconsulta torna algun valor NULL
-
INiANYtornaran les files que coincideixin amb els valorsNOT NULL -
ALLno tornarà cap fila, ja que el valor es compara amb tots els de la llista inclosos elsNULL(i la comparació no tornaràTRUEsinóNULL).
GROUP BY i HAVING
Com és de suposar també podem utilitzar subconsultes de diverses files en la clàusula HAVING.
Subconsultes de diverses columnes
Les subconsultes poden retornar una o diverses columnes.
En aquest cas les comparacions les farem com si fossin tuples de valors:
Exemple: Empleat que cobra més de cada departament
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE (department_id, salary) IN
(SELECT department_id, MAX(salary)
FROM employees
GROUP BY DEPARTMENT_ID);
+-----------+---------+----------+---------------+
| last_name | job_id | salary | department_id |
+-----------+---------+----------+---------------+
| King | AD_PRES | 24000.00 | 90 |
| Hunold | IT_PROG | 9000.00 | 60 |
| Mourgos | ST_MAN | 5800.00 | 50 |
| Abel | SA_REP | 11000.00 | 80 |
| Whalen | AD_ASST | 4400.00 | 10 |
| Hartstein | MK_MAN | 13000.00 | 20 |
| Higgins | AC_MGR | 12000.00 | 110 |
+-----------+---------+----------+---------------+