Salta el contingut
 

Consultes amb m煤ltiples taules

Joan Puigcerver Ib谩帽ez

j.puigcerveribanez@edu.gva.es

Llic猫ncia: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 馃叚

License

Introducci贸露

Fins ara hem realitzat consultes SELECT a partir una 煤nica taula (FROM).

No obstant aix貌, les bases de dades relacionals estan pensades per relacionar les dades de m煤ltiples taules, navegant les claus foranes i combinant la informaci贸 per extraure dades que complixen condicions m茅s complexes.

Hi ha diferents maneres de combinar m煤ltiples taules, per貌 la m茅s elegant 茅s mitjan莽ant la cl脿usula JOIN.

Documentaci贸

Documentaci贸 oficial sobre JOIN:

Taules i dades d'exemple露

Tots els exemples es realitzaran amb les seg眉ents taules i dades: 

CREATE DATABASE tenda;
USE tenda;

CREATE TABLE categoria (
    id_categoria INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(50) NOT NULL
);
CREATE TABLE producte (
    id_producte INT PRIMARY KEY AUTO_INCREMENT,
    id_categoria INT,
    nom VARCHAR(50) NOT NULL,
    preu DECIMAL(5,2) NOT NULL,
    FOREIGN KEY (id_categoria) REFERENCES categoria (id_categoria) 
);

INSERT INTO categoria (id_categoria, nom) VALUES
    (1, "Forn"),
    (2, "Beguda"),
    (3, "Higiene"),
    (4, "Fruta");
INSERT INTO producte (id_producte, id_categoria, nom, preu) VALUES
    (1, 1, "Barra de p脿", 0.7),
    (2, 1, "Past铆s de xocolate", 5.3),
    (3, 2, "Botella d'aigua 50cL", 0.8),
    (4, NULL, "Piles AA", 1.5),
    (5, 4, "Pera", 0.3),
    (6, 4, "Pl脿tan", 0.5);

Figura 2.1: Taula Catgoria amb les seues dades
Taula Categoria
ID Categoria Nom
1 Forn
2 Beguda
3 Higiene
4 Fruta
Figura 2.2: Taula Producte amb les seues dades
Taula Producte
ID Producte ID Categoria Nom Preu
1 1 Barra de p脿 0.70
2 1 Past铆s de xocolate 5.30
3 2 Botella d'aigua 50cL 0.80
4 NULL Piles AA 1.50
5 4 Pera 0.30
6 4 Pl脿tan 0.50

Producte cartesi脿 o CROSS JOIN露

Sempre que es desitge combinar m煤ltiples taules, el SGBD realitzar脿 el producte cartesi脿 entre les taules, que despr茅s filtrar脿 per obtindre el resultat esperat.

Producte cartesi脿 entre dos conjunts
Figura 1: Producte cartesi脿 entre dos conjunts

El producte cartesi脿 retorna una taula on cada element d'una taula estar脿 combinat amb cadascun dels elements de l'altra taula.

Exemple

Figura 3.2: Resultat del producte cartesi脿 entre les taules Producte i Categoria
Taula Categoria Taula Producte
ID Categoria Nom ID Producte ID Categoria Nom Preu
1 Forn 1 1 Barra de p脿 0.70
1 Forn 2 1 Past铆s de xocolate 5.30
1 Forn 3 2 Botella d'aigua 50cL 0.80
1 Forn 4 NULL Piles AA 1.50
1 Forn 5 4 Pera 0.30
1 Forn 6 4 Pl脿tan 0.50
2 Beguda 1 1 Barra de p脿 0.70
2 Beguda 2 1 Past铆s de xocolate 5.30
2 Beguda 3 2 Botella d'aigua 50cL 0.80
2 Beguda 4 NULL Piles AA 1.50
2 Beguda 5 4 Pera 0.30
2 Beguda 6 4 Pl脿tan 0.50
3 Higiene 1 1 Barra de p脿 0.70
3 Higiene 2 1 Past铆s de xocolate 5.30
3 Higiene 3 2 Botella d'aigua 50cL 0.80
3 Higiene 4 NULL Piles AA 1.50
3 Higiene 5 4 Pera 0.30
3 Higiene 6 4 Pl脿tan 0.50
4 Fruta 1 1 Barra de p脿 0.70
4 Fruta 2 1 Past铆s de xocolate 5.30
4 Fruta 3 2 Botella d'aigua 50cL 0.80
4 Fruta 4 NULL Piles AA 1.50
4 Fruta 5 4 Pera 0.30
4 Fruta 6 4 Pl脿tan 0.50

El SGBD retornar脿 el producte cartesi脿 entre dues taules si:

  • Realitzem un JOIN o CROSS JOIN sense cap condici贸. !!! example "Exemple" 

    SELECT * FROM categoria
CROSS JOIN producte;

  • Realitzem una consulta amb m煤ltiples taules en la cl脿usula FROM. !!! example "Exemple" 

    SELECT * FROM categoria, producte;

Combinaci贸 interna o INNER JOIN露

El producte cartesi脿 ens retorna totes les possibles combinacions entre diferents taules i molts dels resultats no estan relacionats entre s铆. Per filtrar els resultats i retornar sols aquells registres que s铆 que estan relacionats entre si, cal utilitzar INNER JOIN, que permet especificar una condici贸 per especificar com es relacionen els registres de les diferents taules. Normalment aquesta condici贸 utilitza les claus foranes definides.

Intersecci贸 entre dos conjunts
Figura 2: Intersecci贸 entre dos conjunts

La cl脿usula INNER JOIN t茅 la seg眉ent sintaxi: 

SELECT columns
    FROM table1 t1
    INNER JOIN table2 t2
        ON t1.attr = t2.attr

  • INNER JOIN: Especifica la taula que es dessitja combinar. Es pot especificar un 脿lies.
  • ON: Especifica els atributs que formen la relaci贸 entre les dues taules. Especifica la condici贸 de filtratge.

Informaci贸

Es poden definir 脿lies en les taules per poder referenciar les taules d'una maner脿 m茅s f脿cil i c貌moda.

Exemple

La intersecci贸 entre les taules Categoria i Producte ens retorna els productes relacionats amb les categories als quals pertanyen.

Figura 4.2: Resultat del INNER JOIN entre les taules Producte i Categoria
Taula Categoria Taula Producte
ID Categoria Nom ID Producte ID Categoria Nom Preu
1 Forn 1 1 Barra de p脿 0.70
1 Forn 2 1 Past铆s de xocolate 5.30
2 Beguda 3 2 Botella d'aigua 50cL 0.80
4 Fruta 5 4 Pera 0.30
4 Fruta 6 4 Pl脿tan 0.50

Aquest resultat es pot obtindre mitjan莽ant la consulta: 

SELECT c.id_categoria, c.nom, p.id_producte, p.id_categoria, p.nom, p.preu
    FROM categoria c
    INNER JOIN producte p
        ON p.id_categoria = c.id_categoria;
S'ha especificat en la condici贸 ON que relacione la taula Producte amb la taula Categoria mitjan莽ant la clau forana id_categoria.

Combinaci贸 externa: LEFT JOIN露

La combinaci贸 externa LEFT JOIN retorna aquells registres que estan relacionats en les dues taules (INNER JOIN), per貌 tamb茅 retorna els valors de la primera taula que no estan relacionats amb elements de la segona taula.

LEFT JOIN entre dos conjunts
Figura 3: LEFT JOIN entre dos conjunts

La cl脿usula LEFT JOIN t茅 una sintaxi equivalent a INNER JOIN: 

SELECT columns
    FROM table1 t1
    LEFT JOIN table2 t2
        ON t1.attr = t2.attr

Exemple

El LEFT JOIN entre les taules Categoria i Producte ens retorna les categories relacionades amb els productes que tenen, per貌 tamb茅 aquelles categories que no tenen cap producte.

Figura 5.2: Resultat del LEFT JOIN entre les taules Producte i Categoria
Taula Categoria Taula Producte
ID Categoria Nom ID Producte ID Categoria Nom Preu
1 Forn 1 1 Barra de p脿 0.70
1 Forn 2 1 Past铆s de xocolate 5.30
2 Beguda 3 2 Botella d'aigua 50cL 0.80
3 Higiene NULL NULL NULL NULL
4 Fruta 5 4 Pera 0.30
4 Fruta 6 4 Pl脿tan 0.50

Aquest resultat es pot obtindre mitjan莽ant la consulta: 

SELECT c.id_categoria, c.nom, p.id_producte, p.id_categoria, p.nom, p.preu
    FROM categoria c
    LEFT JOIN producte p
        ON p.id_categoria = c.id_categoria;

Combinaci贸 externa: RIGHT JOIN露

La combinaci贸 externa RIGHT JOIN retorna aquells registres que estan relacionats en les dues taules (INNER JOIN), per貌 tamb茅 retorna els valors de la segona taula que no estan relacionats amb elements de la primera taula.

脡s exactament el mateix que un LEFT JOIN per貌 en el altre sentit.

RIGHT JOIN entre dos conjunts
Figura 4: RIGHT JOIN entre dos conjunts

La cl脿usula RIGHT JOIN t茅 una sintaxi equivalent a INNER JOIN: 

SELECT columns
    FROM table1 t1
    RIGHT JOIN table2 t2
        ON t1.attr = t2.attr

Exemple

El RIGHT JOIN entre les taules Categoria i Producte ens retorna les categories relacionades amb els productes que tenen, per貌 tamb茅 aquells productes que no tenen cap categoria.

Figura 6.2: Resultat del RIGHT JOIN entre les taules Producte i Categoria
Taula Categoria Taula Producte
ID Categoria Nom ID Producte ID Categoria Nom Preu
1 Forn 1 1 Barra de p脿 0.70
1 Forn 2 1 Past铆s de xocolate 5.30
2 Beguda 3 2 Botella d'aigua 50cL 0.80
NULL NULL 4 NULL Piles AA 1.50
4 Fruta 5 4 Pera 0.30
4 Fruta 6 4 Pl脿tan 0.50

Aquest resultat es pot obtindre mitjan莽ant la consulta: 

SELECT c.id_categoria, c.nom, p.id_producte, p.id_categoria, p.nom, p.preu
    FROM categoria c
    RIGHT JOIN producte p
        ON p.id_categoria = c.id_categoria;

Combinaci贸 externa: FULL JOIN露

La combinaci贸 externa FULL JOIN retorna aquells registres que estan relacionats en les dues taules (INNER JOIN), per貌 tamb茅 retorna els valors de de les dues taules que no estan relacionats amb elements de l'altra taula (LEFT JOIN i RIGHT JOIN).

FULL JOIN entre dos conjunts
Figura 5: FULL JOIN entre dos conjunts

No existeix la cl脿usula FULL JOIN, per貌 podem obtindre el resultat mitjan莽ant la cl脿usula UNION, que retorna els registres presents en dues consultes: 

SELECT columns
    FROM table1 t1
    LEFT JOIN table2 t2
        ON t1.attr = t2.attr
UNION
SELECT columns
    FROM table1 t1
    RIGHT JOIN table2 t2
        ON t1.attr = t2.attr

Exemple

El FULL JOIN entre les taules Categoria i Producte ens retorna les categories relacionades amb els productes que tenen, per貌 tamb茅 aquells productes que no tenen cap categoria i les categories que no tenen cap producte.

Figura 7.2: Resultat del FULL JOIN entre les taules Producte i Categoria
Taula Categoria Taula Producte
ID Categoria Nom ID Producte ID Categoria Nom Preu
1 Forn 1 1 Barra de p脿 0.70
1 Forn 2 1 Past铆s de xocolate 5.30
2 Beguda 3 2 Botella d'aigua 50cL 0.80
3 Higiene NULL NULL NULL NULL
NULL NULL 4 NULL Piles AA 1.50
4 Fruta 5 4 Pera 0.30
4 Fruta 6 4 Pl脿tan 0.50

Aquest resultat es pot obtindre mitjan莽ant la consulta: 

SELECT c.id_categoria, c.nom, p.id_producte, p.id_categoria, p.nom, p.preu
    FROM categoria c
    LEFT JOIN producte p
        ON p.id_categoria = c.id_categoria
UNION
SELECT c.id_categoria, c.nom, p.id_producte, p.id_categoria, p.nom, p.preu
    FROM categoria c
    RIGHT JOIN producte p
        ON p.id_categoria = c.id_categoria;

Bibliografia露

馃搶 Aquest document pot quedar desactualitzat despr茅s d鈥檌mprimir-lo. Pots consultar la versi贸 m茅s recent a la p脿gina web.
馃尶 Abans d鈥檌mprimir aquest document, considera si 茅s realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 馃叚 Joan Puigcerver Ib谩帽ez