Consultes avançades - JOIN - NATURAL JOIN - CROSS 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ó)

NATURAL JOIN

Utilitzarem el NATURAL JOIN només quan les dues taules a unir tinguin únicament amb el mateix nom aquelles columnes que són clau primària (PK) en una i clau forana (FK) en l'altra.

En els nostres exemples podrem unir:

EMPLOYEES i JOBS pel camp JOB_ID
REGIONS i COUNTRIES pel camp REGION_ID
COUNTRIES i LOCATIONS pel camp COUNTRY_ID
LOCATIONS i DEPARTMENTS pel camp LOCATION_ID

En els nostres exemples NO podrem unir:

EMPLOYEES i DEPARTMENTS ja que el camp MANAGER_ID existeix en les dues taules i no són PK i FK respectivament.
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 NATURAL JOIN

FROM < taula1 > NATURAL JOIN < taula2>

Exemple

SELECT REGION_NAME, COUNTRY_ID, COUNTRY_NAME
FROM REGIONS NATURAL JOIN COUNTRIES;
+-------------+------------+--------------------------+
| REGION_NAME | COUNTRY_ID | COUNTRY_NAME             |
+-------------+------------+--------------------------+
| Americas    | CA         | Canada                   |
| Europe      | DE         | Germany                  |
| Europe      | UK         | United Kingdom           |
| Americas    | US         | United States of America |
+-------------+------------+--------------------------+

La consulta ha enllaçat les files de les taules REGIONS i COUNTRIES on REGIONS.REGION_ID = COUNTRIES.REGION_ID

Exemples NATURAL JOIN

Exemple 1

SELECT LAST_NAME, JOB_ID, JOB_TITLE
FROM employees NATURAL JOIN jobs
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

Exemple 2

SELECT CITY, COUNTRY_NAME, REGION_NAME
FROM LOCATIONS NATURAL JOIN COUNTRIES
               NATURAL JOIN REGIONS;
+---------------------+--------------------------+-------------+
| 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

CROSS JOIN

FROM < taula1 > [CROSS] JOIN < taula2>

No utilitzarem gairebé mai el CROSS JOIN ja que el que fa és unir totes les files de la taula de l'esquerra amb totes i cadascuna de les files de la taula de la dreta.

La clàusula CROSS és opcional, per tant, sovint no la posem.

D'aquesta manera, si una taula té 10 files i l'altra 20, retornarà 200 files (10 x 20).

Exemple de CROSS JOIN

SELECT last_name, department_name
FROM employees CROSS JOIN departments;

Possible sortida

+-----------+-----------------+
| last_name | department_name |
+-----------+-----------------+
| King      | Administration  |
| King      | Marketing       |
| King      | Shipping        |
| King      | IT              |
| King      | Sales           |
| King      | Executive       |
| King      | Accounting      |
| King      | Contracting     |
| Kochhar   | Administration  |
| Kochhar   | Marketing       |
| Kochhar   | Shipping        |
| Kochhar   | IT              |
| Kochhar   | Sales           |
| Kochhar   | Executive       |
| Kochhar   | Accounting      |
| Kochhar   | Contracting     |
| De Haan   | Administration  |
| De Haan   | Marketing       |
| De Haan   | Shipping        |
| De Haan   | IT              |
| De Haan   | Sales           |
| De Haan   | Executive       |
| De Haan   | Accounting      |
| De Haan   | Contracting     |
| Hunold    | Administration  |
| Hunold    | Marketing       |
| Hunold    | Shipping        |
| Hunold    | IT              |
| Hunold    | Sales           |
| Hunold    | Executive       |
| Hunold    | Accounting      |
| Hunold    | Contracting     |
| Ernst     | Administration  |
| Ernst     | Marketing       |
| Ernst     | Shipping        |
| Ernst     | IT              |
| Ernst     | Sales           |
| Ernst     | Executive       |
| Ernst     | Accounting      |
| Ernst     | Contracting     |
| Lorentz   | Administration  |
| Lorentz   | Marketing       |
| Lorentz   | Shipping        |
| Lorentz   | IT              |
| Lorentz   | Sales           |
| Lorentz   | Executive       |
| Lorentz   | Accounting      |
| Lorentz   | Contracting     |
| Mourgos   | Administration  |
| Mourgos   | Marketing       |
| Mourgos   | Shipping        |
| Mourgos   | IT              |
| Mourgos   | Sales           |
| Mourgos   | Executive       |
| Mourgos   | Accounting      |
| Mourgos   | Contracting     |
| Rajs      | Administration  |
| Rajs      | Marketing       |
| Rajs      | Shipping        |
| Rajs      | IT              |
| Rajs      | Sales           |
| Rajs      | Executive       |
| Rajs      | Accounting      |
| Rajs      | Contracting     |
| Davies    | Administration  |
| Davies    | Marketing       |
| Davies    | Shipping        |
| Davies    | IT              |
| Davies    | Sales           |
| Davies    | Executive       |
| Davies    | Accounting      |
| Davies    | Contracting     |
| Matos     | Administration  |
| Matos     | Marketing       |
| Matos     | Shipping        |
| Matos     | IT              |
| Matos     | Sales           |
| Matos     | Executive       |
| Matos     | Accounting      |
| Matos     | Contracting     |
| Vargas    | Administration  |
| Vargas    | Marketing       |
| Vargas    | Shipping        |
| Vargas    | IT              |
| Vargas    | Sales           |
| Vargas    | Executive       |
| Vargas    | Accounting      |
| Vargas    | Contracting     |
| Zlotkey   | Administration  |
| Zlotkey   | Marketing       |
| Zlotkey   | Shipping        |
| Zlotkey   | IT              |
| Zlotkey   | Sales           |
| Zlotkey   | Executive       |
| Zlotkey   | Accounting      |
| Zlotkey   | Contracting     |
| Abel      | Administration  |
| Abel      | Marketing       |
| Abel      | Shipping        |
| Abel      | IT              |
| Abel      | Sales           |
| Abel      | Executive       |
| Abel      | Accounting      |
| Abel      | Contracting     |
| Taylor    | Administration  |
| Taylor    | Marketing       |
| Taylor    | Shipping        |
| Taylor    | IT              |
| Taylor    | Sales           |
| Taylor    | Executive       |
| Taylor    | Accounting      |
| Taylor    | Contracting     |
| Grant     | Administration  |
| Grant     | Marketing       |
| Grant     | Shipping        |
| Grant     | IT              |
| Grant     | Sales           |
| Grant     | Executive       |
| Grant     | Accounting      |
| Grant     | Contracting     |
| Whalen    | Administration  |
| Whalen    | Marketing       |
| Whalen    | Shipping        |
| Whalen    | IT              |
| Whalen    | Sales           |
| Whalen    | Executive       |
| Whalen    | Accounting      |
| Whalen    | Contracting     |
| Hartstein | Administration  |
| Hartstein | Marketing       |
| Hartstein | Shipping        |
| Hartstein | IT              |
| Hartstein | Sales           |
| Hartstein | Executive       |
| Hartstein | Accounting      |
| Hartstein | Contracting     |
| Fay       | Administration  |
| Fay       | Marketing       |
| Fay       | Shipping        |
| Fay       | IT              |
| Fay       | Sales           |
| Fay       | Executive       |
| Fay       | Accounting      |
| Fay       | Contracting     |
| Higgins   | Administration  |
| Higgins   | Marketing       |
| Higgins   | Shipping        |
| Higgins   | IT              |
| Higgins   | Sales           |
| Higgins   | Executive       |
| Higgins   | Accounting      |
| Higgins   | Contracting     |
| Gietz     | Administration  |
| Gietz     | Marketing       |
| Gietz     | Shipping        |
| Gietz     | IT              |
| Gietz     | Sales           |
| Gietz     | Executive       |
| Gietz     | Accounting      |
| Gietz     | Contracting     |
+-----------+-----------------+