Consultes avançades - JOIN ... USING - JOIN ... ON
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 ... USING ...
Utilitzarem el JOIN ... USING ... quan les dues taules a unir tinguin aquelles columnes que són clau primària (PK) en una i clau forana (FK) en l’altra amb el mateix nom.
En els nostres exemples podrem unir:
-
tots els de
NATURAL JOIN -
EMPLOYEESiDEPARTMENTSpel campDEPARTMENT_ID
En els nostres exemples NO podrem unir:
- Cap taula de la base Hospital ja que no comparteixen cap nom de camp; recorda que tots els camps de cada taula comencen pel nom de la taula.
Sintaxis del JOIN ... USING ...
FROM < taula1 > JOIN < taula2> USING ( camp1, camp2, ... )
SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID);
+-----------+-----------------+
| LAST_NAME | DEPARTMENT_NAME |
+-----------+-----------------+
| Whalen | Administration |
| Hartstein | Marketing |
| Fay | Marketing |
| Mourgos | Shipping |
| Rajs | Shipping |
| Davies | Shipping |
| Matos | Shipping |
| Vargas | Shipping |
| Hunold | IT |
| Ernst | IT |
| Lorentz | IT |
| Zlotkey | Sales |
| Abel | Sales |
| Taylor | Sales |
| King | Executive |
| Kochhar | Executive |
| De Haan | Executive |
| Higgins | Accounting |
| Gietz | Accounting |
+-----------+-----------------+
La consulta ha enllaçat les files de les taules EMPLOYEES i DEPARTMENT on EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
Exemples JOIN ... USING ...
SELECT LAST_NAME, JOB_ID, JOB_TITLE
FROM employees JOIN jobs USING (JOB_ID)
WHERE department_id in (110, 80);
+-----------+------------+----------------------+
| LAST_NAME | JOB_ID | JOB_TITLE |
+-----------+------------+----------------------+
| Zlotkey | SA_MAN | Sales Manager |
| Abel | SA_REP | Sales Representative |
| Taylor | SA_REP | Sales Representative |
| Higgins | AC_MGR | Accounting Manager |
| Gietz | AC_ACCOUNT | Public Accountant |
+-----------+------------+----------------------+
La consulta ha enllaçat les files de les taules EMPLOYEES i JOBS on EMPLOYEES.JOB_ID = JOBS.JOB_ID
SELECT CITY, COUNTRY_NAME, REGION_NAME
FROM LOCATIONS JOIN COUNTRIES USING (COUNTRY_ID)
JOIN REGIONS USING (REGION_ID);
+---------------------+--------------------------+-------------+
| CITY | COUNTRY_NAME | REGION_NAME |
+---------------------+--------------------------+-------------+
| Toronto | Canada | Americas |
| Oxford | United Kingdom | Europe |
| Southlake | United States of America | Americas |
| South San Francisco | United States of America | Americas |
| Seattle | United States of America | Americas |
+---------------------+--------------------------+-------------+
La consulta ha enllaçat les files de les taules LOCATIONS, COUNTRIES i REGIONS on LOCATIONS.COUNTRY_ID_ID = COUNTRIES.COUNTRY_ID AND COUNTRIES.REGION_ID = REGIONS.REGION_ID
JOIN ... ON ...
FROM < taula1 > JOIN < taula2> ON ( condició )
Quan la PK i la FK de les taules que volem unir tenen noms diferents o bé volem unir les taules amb condicions diferents a igualtat de camps, utilitzarem la fórmula JOIN ON.
La condició pot ser simplement d’igualtat de dos camps o bé complexe amb igualtat de varis camps units amb ANDs i Ors o fins i tot de desigualtats.
En el cas del JOIN ON, els camps que igualem poden ser qualssevols que tinguin el mateix tipus.
Si dos camps de dues taules tenen el mateix nom i volem referir-nos a algun d’ells, caldrà referenciar-lo especificant, davant el nom del camp, el nom de la taula a la que ens referim.
Per això solem utilitzar alies en les taules.
SELECT E.DEPARTMENT_ID FROM EMPLOYEES E;
Sintaxis del JOIN ... ON ...
FROM < taula1 > JOIN < taula2> ON ( condició )
Exemple del JOIN ...ON ...
SELECT LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN
DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);
+-----------+---------------+-----------------+
| LAST_NAME | DEPARTMENT_ID | DEPARTMENT_NAME |
+-----------+---------------+-----------------+
| Whalen | 10 | Administration |
| Hartstein | 20 | Marketing |
| Fay | 20 | Marketing |
| Mourgos | 50 | Shipping |
| * * * * | ** | * * * * * |
+-----------+---------------+-----------------+
La consulta ha enllaçat les files de les taules EMPLOYEES i DEPARTMENTS on EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
SELECT E.LAST_NAME Empleat, J.LAST_NAME Manager
FROM employees E JOIN
employees J ON (E.MANAGER_ID = J.EMPLOYEE_ID)
WHERE E.DEPARTMENT_ID IN (110, 80);
+---------+---------+
| Empleat | Manager |
+---------+---------+
| Zlotkey | King |
| Abel | Zlotkey |
| Taylor | Zlotkey |
| Higgins | Kochhar |
| Gietz | Higgins |
+---------+---------+
La consulta ha enllaçat les files de les taules EMPLOYEES i EMPLOYEES on EMPLOYEES.MANAGER_ID = EMPLOYEES.DEPARTMENT_ID
Aquest és un JOIN recursiu.
SELECT last_name, salary, grade_level, lowest_sal, highest_sal
FROM employees JOIN job_grades
ON(salary BETWEEN lowest_sal AND highest_sal)
WHERE DEPARTMENT_ID IN (110, 80);
+-----------+----------+-------------+------------+-------------+
| last_name | salary | grade_level | lowest_sal | highest_sal |
+-----------+----------+-------------+------------+-------------+
| Taylor | 8600.00 | C | 6000 | 9999 |
| Gietz | 8300.00 | C | 6000 | 9999 |
| Zlotkey | 10500.00 | D | 10000 | 14999 |
| Abel | 11000.00 | D | 10000 | 14999 |
| Higgins | 12000.00 | D | 10000 | 14999 |
+-----------+----------+-------------+------------+-------------+
La consulta ha enllaçat les files de les taules EMPLOYEES i JOB_GRADES on EMPLOYEES.SALARY es troba entre els valors JOB_GRADES.LOWEST_SAL i JOB_GRADES.HIGHEST_SAL
Aquest és un JOIN amb una condició de desigualtat.
Unió de més taules
Podem unir tantes taules com vulguem.
En cada unió podem utilitzar un tipus de JOIN diferent.
Vegem-ho en un exemple:
SELECT department_name, city, country_name,
last_name, salary, grade_level
FROM EMPLOYEES
JOIN DEPARTMENTS USING (DEPARTMENT_ID)
NATURAL JOIN LOCATIONS
NATURAL JOIN COUNTRIES
JOIN JOB_GRADES
ON(salary BETWEEN lowest_sal AND highest_sal)
WHERE DEPARTMENT_ID IN (110, 80);
+-----------------+---------+--------------------------+-----------+----------+-------------+
| department_name | city | country_name | last_name | salary | grade_level |
+-----------------+---------+--------------------------+-----------+----------+-------------+
| Sales | Oxford | United Kingdom | Taylor | 8600.00 | C |
| Accounting | Seattle | United States of America | Gietz | 8300.00 | C |
| Sales | Oxford | United Kingdom | Zlotkey | 10500.00 | D |
| Sales | Oxford | United Kingdom | Abel | 11000.00 | D |
| Accounting | Seattle | United States of America | Higgins | 12000.00 | D |
+-----------------+---------+--------------------------+-----------+----------+-------------+
La consulta ha enllaçat les files de les taules EMPLOYEES, DEPARTMENTS, LOCATIONS, COUNTRIES i JOB_GRADES