Skip to content

Les jointures

Les jointures

Les jointures permettent de mettre des tables en relation entre elles. On peut travailler avec des jointures pour faire des DELETE, SELECT et UPDATE. Le nombre de jointures maximal est de 64 dans une requete.

Jointure Internes

Il existe deux syntaxe possible et permettent de trouver les correspondances entre deux tables :

SELECT *
FROM t1,t2
WHERE condition_jointure
SELECT *
FROM t1 [INNER] JOIN t2 {ON|USING} condition_jointure

S’il n’existe pas de condition de jointures, on obtient un produit cartésien : pour une ligne de la table gauche, on obtient chacune des lignes de la table de droite

Conditions de jointures

Un condition de jointure n’est pas forcément une égalité. La condition de jointure indique l’élément de référence par rapport à la table de gauche. Autrement dit, la table de gauche doit disposer dune clé étrangere et cette derniere doit etre la clé primaire de la table de droite.

Exemple pour la base world.sql (de mysql.com)

SELECT name,Language
FROM countrylanguage AS cl, country AS co
WHERE co.code=cl.countrycode
AND	name LIKE 'T%'
ORDER BY name;
SELECT name,Language
FROM countrylanguage AS cl INNER JOIN country AS co
ON co.code=cl.countrycode
WHERE name LIKE 'T%'
ORDER BY name;
SELECT col1,col2
FROM t1 INNER JOIN t2
ON ( t1.col3 = t2.col3 ) AND (t1.col4 = t2.col4);

On utilisera USING si et seulement si on retrouve le meme champ dans les deux tables :

SELECT col1,col2
FROM t1 INNER JOIN t2
USING ( col3[,...] );

Jointure Externes

Elles permettent de trouver les correspondances et les non-correspondances entre deux tables. Il existe deux types de jointures externes :

  • Gauche, standard SQL
  • Droite

En changeant l’ordre des tables, une jointure gauche devient une jointure droite

LEFT JOIN

Un LEFT JOINT produit au moins une ligne de résultat pour chaques lignes de la table de gauche.

Pour chaques lignes de la table de droite, on affichera NULL s’il n’ya pas de correspondance

RIGHT JOIN

Le RIGHT JOIN produit au moins une ligne de resultat pour chaques lignes de la table de droite. Pour chaque chaqmp de la table de gauche on affichera NULL s’il n’y a pas de correspondance.

Tests

Tables :

CREATE TABLE personnes (
	id mediumint AUTO_INCREMENT PRIMARY KEY,
	nom varchar(10)
);
CREATE TABLE telephones (
	id mediumint AUTO_INCREMENT PRIMARY KEY,
	phone char(10)
);
INSERT INTO personnes(nom) VALUES('Pierre'),('Paul'),('Jacques'),('Marc');
INSERT INTO telephones(phone) VALUES('0123456789'),('0123498789'),('6523456789'),('0123026789'),('0123696789');

Jointure interne :

mysql> SELECT nom,phone
    -> FROM personnes
    -> INNNER JOIN telephones USING (id);
+---------+------------+
| nom     | phone      |
+---------+------------+
| Pierre  | 0123456789 |
| Paul    | 0123498789 |
| Jacques | 6523456789 |
| Marc    | 0123026789 |
+---------+------------+
4 rows in set (0.01 sec)

LEFT JOIN

mysql> SELECT nom,phone
    -> FROM personnes
    -> LEFT JOIN telephones USING (id);
+---------+------------+
| nom     | phone      |
+---------+------------+
| Pierre  | 0123456789 |
| Paul    | 0123498789 |
| Jacques | 6523456789 |
| Marc    | 0123026789 |
+---------+------------+
4 rows in set (0.01 sec)

RIGHT JOIN

mysql> SELECT nom,phone
    -> FROM personnes RIGHT JOIN telephones
    -> USING (id);
+---------+------------+
| nom     | phone      |
+---------+------------+
| Pierre  | 0123456789 |
| Paul    | 0123498789 |
| Jacques | 6523456789 |
| Marc    | 0123026789 |
| NULL    | 0123696789 |
+---------+------------+
5 rows in set (0.00 sec)

On transforme un LEFT JOIN en RIGHT JOIN :

mysql> SELECT nom,phone
    -> FROM telephones LEFT JOIN personnes
    -> USING (id);
+---------+------------+
| nom     | phone      |
+---------+------------+
| Pierre  | 0123456789 |
| Paul    | 0123498789 |
| Jacques | 6523456789 |
| Marc    | 0123026789 |
| NULL    | 0123696789 |
+---------+------------+
5 rows in set (0.00 sec)

RIGHT JOIN en LEFT JOIN :

mysql> SELECT nom,phone
    -> FROM telephones RIGHT JOIN personnes
    -> USING (id);
+---------+------------+
| nom     | phone      |
+---------+------------+
| Pierre  | 0123456789 |
| Paul    | 0123498789 |
| Jacques | 6523456789 |
| Marc    | 0123026789 |
+---------+------------+
4 rows in set (0.00 sec)

Options

Pour les jointures de type interne, il est possible de spécifier l’option NATURAL. Cette option permet de ne pas nommer les colonnes à joindre. MySQL le resoud pour nous en reprenant les memes noms de champs et de meme types

SELECT nom,phone FROM telephones NATURAL {RIGHT|LEFT} JOIN personnes;
mysql> SELECT nom,phone FROM telephones NATURAL LEFT JOIN personnes;
+---------+------------+
| nom     | phone      |
+---------+------------+
| Pierre  | 0123456789 |
| Paul    | 0123498789 |
| Jacques | 6523456789 |
| Marc    | 0123026789 |
| NULL    | 0123696789 |
+---------+------------+
5 rows in set (0.00 sec)

Produit cartésien

mysql> SELECT * FROM telephones,personnes;
+----+------------+----+---------+
| id | phone      | id | nom     |
+----+------------+----+---------+
|  1 | 0123456789 |  1 | Pierre  |
|  3 | 6523456789 |  1 | Pierre  |
|  4 | 0123026789 |  1 | Pierre  |
|  1 | 0123456789 |  3 | Jacques |
|  3 | 6523456789 |  3 | Jacques |
|  4 | 0123026789 |  3 | Jacques |
|  1 | 0123456789 |  4 | Marc    |
|  3 | 6523456789 |  4 | Marc    |
|  4 | 0123026789 |  4 | Marc    |
+----+------------+----+---------+
9 rows in set (0.00 sec)

Les joitnures avec UPDATE

Elles permettent de modifier plusieurs tables de facon dynamique. Tres utile pour les moteurs de stockages qui ne respectent pas les transactions. On peut simuler des clé étrangeres en cascade.

UPDATE t1 type_jointure t2 SET t1.col1=x , t2.col2=y[,...]

ATTENTION : On ne peut plus utiliser un ORDER BY et un LIMIT.

Exemple de modification en cascade

UPDATE personnes INNER JOIN telephones USING(id)
SET personnes.id=personnes.id*10,
telephones.id=telephones.id*10;

Syntaxe de modification avec restriction :

//Mise à jour si et seulement si l'update trouve des données
UPDATE personnes INNER JOIN telephones USING(id)
SET personnes.id=personnes.id*100
WHERE telephones.id IS NULL;

Mise a jour de l’id du telephone qui n’est pas rattaché a une personne :

UPDATE personnes RIGHT JOIN telephones USING(id)
SET telephones.id=telephones.id*100
WHERE personnes.id IS NULL;

Les jointures avec DELETE

Elles permettent de supprimer des données sur plusieurs tables dynamiquement.

DELETE t1[,t2]
FROM t1 type_jointure t2
WHERE condition
//OU
DELETE t1,t2
USING jointure

ATTENTION : On ne peut plus utiliser un ORDER BY et un LIMIT.

Pour les DELETE, le nombre des jointures possible se limite à 50 tables

DELETE telephones
FROM personnes RIGHT JOIN telephones USING(id)
WHERE personnes.id IS NULL;

Table avec jointure sur elle meme

Il est souvent utile de joindre une table sur elle même. Pour ce faire, dans la clause du FROM, on devra faire appel a cette meme table mais avec des alias différents

DROP TABLE self_join;
CREATE TABLE self_join (
	id mediumint AUTO_INCREMENT,
	keyword varchar(10),
	PRIMARY KEY (id,keyword)
);
INSERT INTO self_join(keyword) VALUES ('MySQL'),('4.0'),('test'),('MySQL'),('4.0');
/*On veut retrouver les mots clés mysql et 4.0*/
SELECT sj1.id,sj1.keyword
FROM self_join AS sj1 INNER JOIN self_join AS sj2
ON (sj1.id=sj2.id) AND (sj1.keyword=sj2.keyword)
WHERE sj1.keyword='MySQL'
OR sj1.keyword='4.0';
/*Ou*/
SELECT sj1.id,sj1.keyword
FROM self_join AS sj1
NATURAL JOIN self_join AS sj2
WHERE sj1.keyword='MySQL'
OR sj1.keyword='4.0';
/*Ou*/
SELECT sj1.id,sj1.keyword
FROM self_join AS sj1 INNER JOIN self_join AS sj2
ON (sj1.id=sj2.id) AND (sj1.keyword=sj2.keyword) AND (sj1.keyword='MySQL' OR sj2.keyword='4.0');

Comment fonctionne une requête

MySQL décrypte une requete dans l’ordre suivant :

  1. FROM
  2. WHERE
  3. Crée des ALIAS
  4. GROUP BY (+ WITH ROLLUP)
  5. SELECT
  6. HAVING
  7. ORDER BY
  8. LIMIT