LEFT JOIN, RIGHT JOIN

LEFT JOIN

• https://www.sqltutorial.org/sql-left-join/

No tots els països tenen ciutats associades. La informació de les ciutats està en la taula location. Si volem veure TOTS els països, i també les ciutats d'aquells països que en tenen, haurem de fer un LEFT JOIN on el country està en el cantó esquerre:

SELECT c.country_name, c.country_id, l.country_id, l.city
FROM countries c
LEFT JOIN locations l
ON l.country_id = c.country_id;

+--------------------------+------------+------------+---------------------+
| country_name             | country_id | country_id | city                |
+--------------------------+------------+------------+---------------------+
| Argentina                | AR         | NULL       | NULL                |
| Australia                | AU         | NULL       | NULL                |
| Belgium                  | BE         | NULL       | NULL                |
| Brazil                   | BR         | NULL       | NULL                |
| Canada                   | CA         | CA         | Toronto             |
| Switzerland              | CH         | NULL       | NULL                |
| China                    | CN         | NULL       | NULL                |
| Germany                  | DE         | DE         | Munich              |
| Denmark                  | DK         | NULL       | NULL                |
...

Podem modificar la consulta per veure quins països no tenen cap ciutat:

SELECT c.country_name, c.country_id
FROM countries c
LEFT JOIN locations l
ON l.country_id = c.country_id
WHERE city IS NULL;

+--------------+------------+
| country_name | country_id |
+--------------+------------+
| Argentina    | AR         |
| Australia    | AU         |
| Belgium      | BE         |
| Brazil       | BR         |
| Switzerland  | CH         |
...

Podem fer un doble LEFT JOIN amb les taules regions, countries i locations:

SELECT r.region_name, c.country_name, l.street_address, l.city
FROM regions r
LEFT JOIN countries c ON c.region_id = r.region_id
LEFT JOIN locations l ON l.country_id = c.country_id;

+------------------------+--------------------------+------------------------------------------+---------------------+
| region_name            | country_name             | street_address                           | city                |
+------------------------+--------------------------+------------------------------------------+---------------------+
| Europe                 | Belgium                  | NULL                                     | NULL                |
| Europe                 | Switzerland              | NULL                                     | NULL                |
| Europe                 | Germany                  | Schwanthalerstr. 7031                    | Munich              |
| Europe                 | Denmark                  | NULL                                     | NULL                |
| Europe                 | France                   | NULL                                     | NULL                |
...

Podria donar-se el cas de què hi haguessin regions que no tenen països, i aleshores també sortirien a la llista.

RIGHT JOIN

És exactament el mateix que el LEFT JOIN, però el paper de la taula esquerra i dreta canvien.

Per fer la mateixa select que abans podem fer:

SELECT c.country_name, c.country_id, l.country_id, l.city
FROM locations l
RIGHT JOIN countries c
ON l.country_id = c.country_id;

+--------------------------+------------+------------+---------------------+
| country_name             | country_id | country_id | city                |
+--------------------------+------------+------------+---------------------+
| Argentina                | AR         | NULL       | NULL                |
| Australia                | AU         | NULL       | NULL                |
| Belgium                  | BE         | NULL       | NULL                |
| Brazil                   | BR         | NULL       | NULL                |
| Canada                   | CA         | CA         | Toronto             |
| Switzerland              | CH         | NULL       | NULL                |
...

FULL OUTER JOIN: no existeix a MySQL

Podria passar que hi haguessin països que no tinguessin ciutats, i ciutats que no tinguessin països. Aleshores, si volguéssim veure tots els països i totes les ciutats, encara que no tinguin ciutat o país associat, podem fer un FULL OUTER JOIN. En MySQL no exiteix com a tal, però es fa ajuntant un LEFT JOIN i un RIGHT JOIN.

Primer de tot, provoquem que hi hagi locations que no tinguin un país associat, permetent valors nuls a country_id:

ALTER TABLE locations MODIFY country_id CHAR(2) NULL;

update locations set country_id=NULL where city IN ('Seattle','Toronto','Munich');

I la consulta queda:

SELECT c.country_name, c.country_id, l.country_id, l.city
FROM countries c
LEFT JOIN locations l
ON l.country_id = c.country_id
UNION 
SELECT c.country_name, c.country_id, l.country_id, l.city
FROM countries c
RIGHT JOIN locations l
ON l.country_id = c.country_id;

+--------------------------+------------+------------+---------------------+
| country_name             | country_id | country_id | city                |
+--------------------------+------------+------------+---------------------+
...
| Netherlands              | NL         | NULL       | NULL                |
| Singapore                | SG         | NULL       | NULL                |
| United Kingdom           | UK         | UK         | Oxford              |
| United Kingdom           | UK         | UK         | London              |
| United States of America | US         | US         | South San Francisco |
| United States of America | US         | US         | Southlake           |
| Zambia                   | ZM         | NULL       | NULL                |
| Zimbabwe                 | ZW         | NULL       | NULL                |
| NULL                     | NULL       | NULL       | Seattle             |
| NULL                     | NULL       | NULL       | Toronto             |
| NULL                     | NULL       | NULL       | Munich              |
+--------------------------+------------+------------+---------------------+

---

creat per Joan Quintana Compte, febrer 2022