Diferència entre revisions de la pàgina «Introducció a combinar taules»

De wikijoan
Salta a la navegació Salta a la cerca
 
(Hi ha una revisió intermèdia del mateix usuari que no es mostren)
Línia 126: Línia 126:
 
</pre>
 
</pre>
 
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''.
 
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''.
 +
==enllaçar més de dues taules==
 +
<pre>
 +
municipis de Cuenca
 +
--------------------
 +
select municipi, provincia from
 +
municipis m INNER JOIN provincies p
 +
ON m.id_prov=p.id_prov
 +
WHERE provincia='Cuenca'
 +
</pre>
 +
Per trobar els municipis de Castilla la Mancha, necessito enllaçar ''municipis'' amb ''provínices'' i amb ''comunitats''.
 +
<pre>
 +
municipis de Castilla la Mancha
 +
-------------------------------
 +
select municipi, provincia,comunitat from
 +
(municipis m INNER JOIN provincies p
 +
ON m.id_prov=p.id_prov) INNER JOIN comunitats c
 +
ON p.id_com=c.id_com
 +
WHERE comunitat='Castilla la Mancha'
 +
</pre>
  
 
=T6: inner join=
 
=T6: inner join=
−
Comencem mirant els ''àlies'' i continuem amb la teoria:
+
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.
 
*https://www.sqltutorial.org/sql-alias/
 
*https://www.sqltutorial.org/sql-alias/
 
<pre>
 
<pre>
 +
# 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);
 
</pre>
 
</pre>
  
 
{{Autor}}, novembre 2021
 
{{Autor}}, novembre 2021

Revisió de 14:55, 24 nov 2021

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.

enllaçar més de dues taules

municipis de Cuenca
--------------------
select municipi, provincia from
municipis m INNER JOIN provincies p
ON m.id_prov=p.id_prov
WHERE provincia='Cuenca'

Per trobar els municipis de Castilla la Mancha, necessito enllaçar municipis amb provínices i amb comunitats. 

municipis de Castilla la Mancha
-------------------------------
select municipi, provincia,comunitat from
(municipis m INNER JOIN provincies p
ON m.id_prov=p.id_prov) INNER JOIN comunitats c
ON p.id_com=c.id_com
WHERE comunitat='Castilla la Mancha'

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

Obtingut de «http://wiki.joanillo.org/index.php?title=Introducció_a_combinar_taules&oldid=298054»