Consultes avançades - JOIN

Fins ara totes les comandes SELECT feien referència a dades d’una sola taula.

A la informació d'una taula, sovint, voldrem afegir informació que trobarem en altres taules amb les quals hi ha una relació a través de claus foranes que apunten cap a les seves claus primàries.

Aquestes comandes utilitzen la directiva JOIN al FROM de la consulta.

JOIN

Podem afegir columnes de taules amb les que «estem relacionats» amb la clàusula JOIN.

Depenent del nom de les columnes que comparteixen les taules a «lligar», tindrem tres formes diferents de fer el JOIN:

• <taula> NATURAL JOIN <taula>

• <taula> JOIN <taula> USING (col1, ...)

• <taula> JOIN <taula> ON (condició)

JOIN d'una taula amb ella mateixa

De vegades, en el cas de relacions recursives, podem haver d’unir una taula amb sí mateixa i per tant fer un JOIN amb la mateixa taula.

En aquest cas caldrà diferenciar quan parlem dels camps d’un costat o de l’altre, per la qual cosa utilitzarem, sempre, **alies per les taules.

Aquests alies solen tenir una o dues lletres: employees e, departments d, jobs j, job_history jh, etc.

Sintaxis

FROM < taula1 > t1 JOIN 
     < taula1 > t2 ON
        (t1.camp1 = t2.camp2
        [AND t1.camp11 = t2.camp21 ...]
        )

En el nostre exemple tenim la taula EMPLOYEES amb el camp MANAGER_ID que referencia EMPLOYEE_ID de la mateixa taula EMPLOYEES.

Gràficament podem expressar-ho així.

La relacio	equival a	La relació
	equival a

Això significa que si vull unir la taula EMPLOYEES i la taula MANAGER posaria la següent consulta:

taules EMPLOYEES i MANAGER

SELECT E.LAST_NAME, M.LAST_NAME
FROM EMPLOYEES E JOIN MANAGER M
                 ON E.MANAGER_ID = M.EMPLOYEE_ID

Si tot ho tenim a la taula EMPLOYEES, la consulta quedarà de la següent forma:

taula EMPLOYEES

SELECT E.LAST_NAME, M.LAST_NAME
FROM EMPLOYEES E JOIN EMPLOYEES M
                 ON E.MANAGER_ID = M.EMPLOYEE_ID

Només hem canviat la taula MANAGERper EMPLOYEES. Això sí, era necessari tenir Alies a les taules.

Així tindrem als camps de E, els de l’empleat i als de M els del seu cap: E.LAST_NAME, M.LAST_NAME, ...

Exemple - Subordinats del Sr. Mourgos

SELECT E.LAST_NAME Empleat, M.LAST_NAME Cap
FROM EMPLOYEES E JOIN EMPLOYEES M
                 ON (E.MANAGER_ID = M.EMPLOYEE_ID)
WHERE M.LAST_NAME = 'Mourgos'
+---------+---------+
| Empleat | Cap     |
+---------+---------+
| Rajs    | Mourgos |
| Davies  | Mourgos |
| Matos   | Mourgos |
| Vargas  | Mourgos |
+---------+---------+

Exemple - Empleats amb el mateix treball que la Sra. Abel

SELECT E.LAST_NAME Empleat, A.LAST_NAME Abel
FROM EMPLOYEES E JOIN EMPLOYEES A
                 USING (JOB_ID)
WHERE A.LAST_NAME = 'Abel' AND 
      A.EMPLOYEE_ID <> E.EMPLOYEE_ID;
+---------+------+
| Empleat | Abel |
+---------+------+
| Taylor  | Abel |
| Grant   | Abel |
+---------+------+

La segona condició del WHERE evita que surti la mateixa Sra. Abel.