Introducció a combinar taules

T5: Combinar comunitats i províncies

Taules comunitats i províncies:

mysql> desc comunitats;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id_com    | smallint    | NO   | PRI | NULL    |       |
| comunitat | varchar(80) | NO   |     | NULL    |       |
| abr_com   | varchar(4)  | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

mysql> desc provincies;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id_prov   | smallint    | NO   | PRI | NULL    |       |
| provincia | varchar(30) | NO   |     | NULL    |       |
| id_com    | smallint    | YES  |     | NULL    |       | -> és la clau forànea
+-----------+-------------+------+-----+---------+-------+

Volem esbrinar les províncies de Castilla la Mancha. Tenim vàries maneres de fer-ho.

1a manera

Primer esbrinem quin és el id_com de Castilla la Mancha. I a partir d'aquest valor, podem trobar les províncies:

mysql> select id_com from comunitats where comunitat= 'Castilla la Mancha';
14

mysql> select id_prov, provincia from provincies where id_com=14;
+---------+-------------+
| id_prov | provincia   |
+---------+-------------+
|       2 | Albacete    |
|      13 | Ciudad Real |
|      16 | Cuenca      |
|      19 | Guadalajara |
|      45 | Toledo      |
+---------+-------------+

2a manera: subconsulta

És la mateixa idea, però ho fem amb un sol pas:

mysql> select * from provincies where id_com = ( select id_com from comunitats where comunitat= 'Castilla la Mancha')

3a manera: combinem les dues taules

Però això s'ha de fer bé. Tenim 17 comunitats i 52 províncies:

select count(*) from comunitats;
17

select count(*) from provincies;
52

Si ho combinem a lo bèstia, obtenim el que se'n diu el producte cartesià. Combinem tot amb tot, que són 17*52 = 988 registres

mysql> select provincia, p.id_com ,comunitat, c.id_com from provincies p, comunitats c;

...
| Zamora                 |     11 | País Valencià      |      3 |
| Zamora                 |     11 | Aragón             |      2 |
| Zamora                 |     11 | Catalunya          |      1 |
| Zaragoza               |      2 | melilla            |     19 |
| Zaragoza               |      2 | Ceuta              |     18 |
| Zaragoza               |      2 | Islas Canarias     |     17 |
| Zaragoza               |      2 | Andalucía          |     16 |
| Zaragoza               |      2 | Murcia             |     15 |
| Zaragoza               |      2 | Castilla la Mancha |     14 |
| Zaragoza               |      2 | Extremadura        |     13 |
| Zaragoza               |      2 | Madrid             |     12 |
| Zaragoza               |      2 | Castilla León      |     11 |
| Zaragoza               |      2 | Galicia            |     10 |
| Zaragoza               |      2 | Asturias           |      9 |
| Zaragoza               |      2 | Cantabria          |      8 |
| Zaragoza               |      2 | Euskadi            |      7 |
| Zaragoza               |      2 | La Rioja           |      6 |
| Zaragoza               |      2 | Navarra            |      5 |
| Zaragoza               |      2 | Illes Balears      |      4 |
| Zaragoza               |      2 | País Valencià      |      3 |
| Zaragoza               |      2 | Aragón             |      2 | -> aquesta és la fila que val
| Zaragoza               |      2 | Catalunya          |      1 |
...
988 rows in set (0,00 sec)

Ja veiem que això no pot ser. Només té sentit la intersecció correcta, que és que el p.id_com coincideixi amb el c.id_com:

select provincia, p.id_com ,comunitat, c.id_com from provincies p, comunitats c where p.id_com=c.id_com;

+------------------------+--------+--------------------+--------+
| provincia              | id_com | comunitat          | id_com |
+------------------------+--------+--------------------+--------+
| Álava                  |      7 | Euskadi            |      7 |
| Albacete               |     14 | Castilla la Mancha |     14 |
| Alacant                |      3 | País Valencià      |      3 |
| Almería                |     16 | Andalucía          |     16 |
| Ávila                  |     11 | Castilla León      |     11 |
| Badajoz                |     13 | Extremadura        |     13 |
| Balears                |      4 | Illes Balears      |      4 |
| Barcelona              |      1 | Catalunya          |      1 |
...
52 rows in set (0,00 sec)

Millor (ordenem per comunitats):
select p.id_com, provincia, comunitat, c.id_com from provincies p, comunitats c where p.id_com=c.id_com order by c.id_com,id_prov;

Ara sí que ja està bé, i ja podem saber les províncies de Castilla la Mancha:

select provincia, p.id_com ,comunitat, c.id_com from provincies p, comunitats c WHERE p.id_com=c.id_com and comunitat='Castilla la Mancha';

+-------------+--------+--------------------+--------+
| provincia   | id_com | comunitat          | id_com |
+-------------+--------+--------------------+--------+
| Albacete    |     14 | Castilla la Mancha |     14 |
| Ciudad Real |     14 | Castilla la Mancha |     14 |
| Cuenca      |     14 | Castilla la Mancha |     14 |
| Guadalajara |     14 | Castilla la Mancha |     14 |
| Toledo      |     14 | Castilla la Mancha |     14 |
+-------------+--------+--------------------+--------+
5 rows in set (0,00 sec)

Aquesta és la manera correcta de combinar les dues taules, i d'aquí ve la importància de les claus forànees com a sistema per navegar la informació entre les taules.

4a manera: combinem les dues taules amb INNER JOIN

Hi ha una manera millor de fer-ho, més correcta, que és utilitzar els JOIN. Concretament, en aquest cas un INNER JOIN:

select provincia, p.id_com ,comunitat, c.id_com from provincies p INNER JOIN comunitats c ON p.id_com=c.id_com WHERE comunitat='Castilla la Mancha';

El resultat és el mateix, però ara distingim entre fer la intersecció correctament amb INNER JOIN, i fer el filtratge de la comunitat amb el WHERE.

T6: inner join

Comencem mirant els àlies i continuem amb la teoria:

• https://www.sqltutorial.org/sql-alias/

---

creat per Joan Quintana Compte, novembre 2021