Introducció a combinar taules
Contingut
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. En aquesta secció només estudiem els INNER JOIN bàsics. Els altres tipus de JOIN (left join, right join, left outer join) els farem més endavant.
# notació tablename.columnname SELECT employees.first_name, employees.last_name FROM employees; # notació database.tablename.columnname SELECT HR.employees.first_name, HR.employees.last_name FROM employees; SELECT e.first_name, e.last_name FROM employees AS e; # i la utilització de àlies ens serà molt útil (imprescindible) quan fem combinació de taules SELECT department_id, department_name FROM departments WHERE department_id IN (1, 2, 3); SELECT first_name, last_name, department_id FROM employees WHERE department_id IN (1, 2, 3) ORDER BY department_id; SELECT first_name, last_name, employees.department_id, departments.department_id, department_name FROM employees INNER JOIN departments ON departments.department_id = employees.department_id WHERE employees.department_id IN (1 , 2, 3); SELECT first_name, last_name, job_title, department_name FROM employees e INNER JOIN departments d ON d.department_id = e.department_id INNER JOIN jobs j ON j.job_id = e.job_id WHERE e.department_id IN (1, 2, 3);
creat per Joan Quintana Compte, novembre 2021
