Consultes avançades - EXISTS i NOT EXISTS

Les clàusules EXISTS i NOT EXISTS poden utilitzar-se per a comprovar coincidències en subconsultes.

EXISTS comprova un resultat CERT o coincident en la subconsulta

Per respondre la consulta: Quins empleats no són caps de ningú? Podem mirar, empleat per empleat, si no és cap d’altres empleats.

SELECT last_name, job_id, salary, department_id
FROM employees emp
WHERE NOT EXISTS 
      (SELECT *
       FROM employees emp2
       WHERE emp.employee_id = emp2.manager_id);
+-----------+------------+----------+---------------+
| last_name | job_id     | salary   | department_id |
+-----------+------------+----------+---------------+
| Ernst     | IT_PROG    |  6000.00 |            60 |
| Lorentz   | IT_PROG    |  4200.00 |            60 |
| Rajs      | ST_CLERK   |  3500.00 |            50 |
| Davies    | ST_CLERK   |  3100.00 |            50 |
| Matos     | ST_CLERK   |  2600.00 |            50 |
| Vargas    | ST_CLERK   |  2500.00 |            50 |
| Abel      | SA_REP     | 11000.00 |            80 |
| Taylor    | SA_REP     |  8600.00 |            80 |
| Grant     | SA_REP     |  7000.00 |          NULL |
| Whalen    | AD_ASST    |  4400.00 |            10 |
| Fay       | MK_REP     |  6000.00 |            20 |
| Gietz     | AC_ACCOUNT |  8300.00 |           110 |
+-----------+------------+----------+---------------+

La subconsulta fa referència a un valor de la consulta principal

Si haguéssim utilitzat NOT IN no haguéssim trobat valors ja que a la llista hi ha algun valor NULL.

SELECT last_name, job_id, salary, department_id
FROM employees
WHERE employee_id NOT IN
      (SELECT manager_id
       FROM employees);
+-----------+------------+----------+---------------+
| last_name | job_id     | salary   | department_id |
+-----------+------------+----------+---------------+
  Empty set

Caldria haver evitat rebre algun valor NULL a la llista...

... afegint, per exemple, la condició MANAGER_ID IS NOT NULL a la subconsulta, quedant així:

SELECT last_name, job_id, salary, department_id
FROM employees
WHERE employee_id NOT IN
    (SELECT manager_id
    FROM employees
    WHERE MANAGER_ID IS NOT NULL);

ATENCIÓ. COMPTE

Compte amb els valors NULL a les subconsultes amb IN i NOT IN.
Si no estem segurs de si una subconsulta inclourà valors NULL podem eliminar-lo mitjançant IS NOT NULL en la clàusula WHERE de la subconsulta.
Algunes subconsultes poden tornar una o vàries files, en aquest cas cal utilitzar operadors de vàries files.
Si ens preguntem qui treballa al mateix departament que Ernst i posem aquesta consulta:
```
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE department_id =
    (SELECT department_id
    FROM employees
    WHERE last_name = 'Ernst')
```
Funcionarà mentre no hi hagi 2 persones amb cognom Ernst a l’empresa. Si trobem 2 persones de cognom Ernst, la consulta fallarà. Sempre que una subconsulta pugui retornar més d’una fila, utilitzarem la fórmula de diverses files.