Salta el contingut

Consultes avançades - LEFT JOIN - RIGHT JOIN - FULL 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

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 EXTERN

Fins ara, quan hem utilitzat la clàusula JOIN, sigui la que sigui, ens ha retornat aquelles files que pertanyien a les dues taules i que quedaven lligades entre elles; això és el que s'anomena un JOIN INTERN.

El JOIN INTERN, en la teoria, porta la clàusula INNER, tot i que, al ser opcinal, no la solem posar.

Exemples de INNER JOIN 
SELECT *
FROM COUNTRIES NATURAL INNER JOIN REGIONS;

SELECT *
FROM COUNTRIES INNER JOIN REGIONS USING (REGION_ID);

SELECT *
FROM COUNTRIES C INNER JOIN REGIONS R ON (C.REGION_ID = R.REGION_ID);

JOIN EXTERN és aquell que és capaç de retornar-nos les files coincidents i també aquelles files d'una, o de les dues, taula que no estan lligades a cap registre de l'altra taula.

El JOIN EXTERN, en la teoria, porta la clàusula OUTER, tot i que, al ser opcinal, no la solem posar.

Exemples de OUTER JOIN 
SELECT *
FROM COUNTRIES NATURAL RIGHT OUTER JOIN REGIONS;

SELECT *
FROM COUNTRIES RIGHT OUTER JOIN REGIONS USING (REGION_ID);

SELECT *
FROM COUNTRIES C RIGHT OUTER JOIN REGIONS R ON (C.REGION_ID = R.REGION_ID);

Trobem tres tipus de JOIN EXTERN:

  • El que ens retorna, a part de les files coincidents, les files de la taula que tenim a l'esquerra de la paraula JOIN que no lliguen amb cap fila de la taula que tenim a la dreta del JOIN. És el LEFT JOIN.

  • El que ens retorna, a part de les files coincidents, les files de la taula que tenim a la dreta de la paraula JOIN que no lliguen amb cap fila de la taula que tenim a l'esquerra del JOIN. És el RIGHT JOIN.

  • El que ens retorna TOTES les files de la taula de l'esquerra del JOIN i TOTES les files de la taula de la dreta del JOIN encara que no lliguin amb cap fila de l'altra taula. És el FULL JOIN.

LEFT [OUTER] JOIN

Quan utilitzen el LEFT JOIN, la consulta ens torna totes les files de la taula de l'esquerra del JOIN.

Per aquelles files de la taula de l'esquerra del JOIN que no tinguin correspondència amb cap fila de la taula dreta del JOIN obtindran el valor NULL per a cadascun dels camps demanats de la taula de la dreta del JOIN.

Exemple de LEFT [OUTER] JOIN

Exemple
SELECT R.REGION_NAME, C.COUNTRY_ID, C.COUNTRY_NAME
FROM REGIONS R NATURAL LEFT JOIN COUNTRIES C;
+------------------------+------------+--------------------------+
| REGION_NAME            | COUNTRY_ID | COUNTRY_NAME             |
+------------------------+------------+--------------------------+
| Americas               | CA         | Canada                   |
| Europe                 | DE         | Germany                  |
| Europe                 | UK         | United Kingdom           |
| Americas               | US         | United States of America |
| Asia                   | NULL       | NULL                     |
| Middle East and Africa | NULL       | NULL                     |
+------------------------+------------+--------------------------+

La consulta ha enllaçat les files de les taules REGIONS i COUNTRIES i també surten les REGIONS sense COUNTRIES amb les columnes de la taula COUNTRIES amb valor NULL

RIGHT [OUTER] JOIN

Quan utilitzen el RIGHT JOIN, la consulta ens torna totes les files de la taula de la dreta del JOIN.

Per aquelles files de la taula de la dreta del JOIN que no tinguin correspondència amb cap fila de la taula esquerra del JOIN obtindran el valor NULL per a cadascun dels camps demanats de la taula de l'esquerra del JOIN.

Exemple de RIGHT [OUTER] JOIN

Exemple
SELECT e.last_name, d.department_id, d.department_name
FROM employees e LEFT JOIN departments d
                 ON (e.department_id = d.department_id);
+-----------+---------------+-----------------+
| last_name | department_id | department_name |
+-----------+---------------+-----------------+
| King      |            90 | Executive       |
| Kochhar   |            90 | Executive       |
| De Haan   |            90 | Executive       |
| Hunold    |            60 | IT              |
| Ernst     |            60 | IT              |
| Lorentz   |            60 | IT              |
| Mourgos   |            50 | Shipping        |
| Rajs      |            50 | Shipping        |
| Davies    |            50 | Shipping        |
| Matos     |            50 | Shipping        |
| Vargas    |            50 | Shipping        |
| Zlotkey   |            80 | Sales           |
| Abel      |            80 | Sales           |
| Taylor    |            80 | Sales           |
| Grant     |          NULL | NULL            |
| Whalen    |            10 | Administration  |
| Hartstein |            20 | Marketing       |
| Fay       |            20 | Marketing       |
| Higgins   |           110 | Accounting      |
| Gietz     |           110 | Accounting      |
+-----------+---------------+-----------------+

La consulta retorna l'Empleat Grant tot i no estar assignat a cap departament. El seu departament és NULL

La taula EMPLOYEES és la que apareix a l'esquerra del JOIN. Per això apareixen TOTS els empleats.

FULL [OUTER] JOIN

Alguns SGBDR permeten escollir TOTES les files de TOTES dues bandes del JOIN. Això en MariaDB i en MySQL no funciona.

Sí funciona el FULL JOIN en ORACLE.