MySQL Tutoriel JOINS : INTÉRIEUR, EXTÉRIEUR, GAUCHE, DROITE, CROIX
Marcus Allen
Que sont les JOINS ?
Les jointures aident à récupérer les données de deux ou plusieurs tables de base de données.
Les tables sont mutuellement liées à l'aide de clés primaires et étrangères.
Remarque : JOIN est le sujet le plus mal compris parmi les amateurs de SQL. Par souci de simplicité et de facilité de compréhension, nous utiliserons une nouvelle base de données pour pratiquer l'échantillon. Comme indiqué ci-dessous
| id | Prénom | nom de famille | id_film |
|---|---|---|---|
| 1 | Adam | Smith | 1 |
| 2 | Ravi | Kumar | 2 |
| 3 | Susan | Davidson | 5 |
| 4 | Jenny | Adrianna | 8 |
| 5 | Lee | Pong | 10 |
| id | titre | category |
|---|---|---|
| 1 | ASSASSIN'S CREED : BRAISES | Animations |
| 2 | Vrai acier (2012) | Animations |
| 3 | Alvin et les Chipmunks | Animations |
| 4 | Les aventures de Tintin | Animations |
| 5 | Coffre-fort (2012) | Action |
| 6 | Maison sûre (2012) | Action |
| 7 | GIA | 18+ |
| 8 | Date limite 2009 | 18+ |
| 9 | L'image sale | 18+ |
| 10 | Marley et moi | Romantique |
Types de jointures
Croix JOIN
Cross JOIN est la forme la plus simple de JOIN qui fait correspondre chaque ligne d'une table de base de données à toutes les lignes d'une autre.
En d’autres termes, cela nous donne des combinaisons de chaque ligne du premier tableau avec tous les enregistrements du deuxième tableau.
Supposons que nous souhaitions obtenir tous les enregistrements de membres par rapport à tous les enregistrements de films, nous pouvons utiliser le script ci-dessous pour obtenir les résultats souhaités.
SELECT * FROM `movies` CROSS JOIN `members`
Exécuter le script ci-dessus dans MySQL établi nous donne les résultats suivants.
| id | title | id | first_name | last_name | movie_id | |
|---|---|---|---|---|---|---|
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 1 | Adam | Smith | 1 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 2 | Ravi | Kumar | 2 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 3 | Susan | Davidson | 5 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 4 | Jenny | Adrianna | 8 |
| 1 | ASSASSIN'S CREED: EMBERS | Animations | 6 | Lee | Pong | 10 |
| 2 | Real Steel(2012) | Animations | 1 | Adam | Smith | 1 |
| 2 | Real Steel(2012) | Animations | 2 | Ravi | Kumar | 2 |
| 2 | Real Steel(2012) | Animations | 3 | Susan | Davidson | 5 |
| 2 | Real Steel(2012) | Animations | 4 | Jenny | Adrianna | 8 |
| 2 | Real Steel(2012) | Animations | 6 | Lee | Pong | 10 |
| 3 | Alvin and the Chipmunks | Animations | 1 | Adam | Smith | 1 |
| 3 | Alvin and the Chipmunks | Animations | 2 | Ravi | Kumar | 2 |
| 3 | Alvin and the Chipmunks | Animations | 3 | Susan | Davidson | 5 |
| 3 | Alvin and the Chipmunks | Animations | 4 | Jenny | Adrianna | 8 |
| 3 | Alvin and the Chipmunks | Animations | 6 | Lee | Pong | 10 |
| 4 | The Adventures of Tin Tin | Animations | 1 | Adam | Smith | 1 |
| 4 | The Adventures of Tin Tin | Animations | 2 | Ravi | Kumar | 2 |
| 4 | The Adventures of Tin Tin | Animations | 3 | Susan | Davidson | 5 |
| 4 | The Adventures of Tin Tin | Animations | 4 | Jenny | Adrianna | 8 |
| 4 | The Adventures of Tin Tin | Animations | 6 | Lee | Pong | 10 |
| 5 | Safe (2012) | Action | 1 | Adam | Smith | 1 |
| 5 | Safe (2012) | Action | 2 | Ravi | Kumar | 2 |
| 5 | Safe (2012) | Action | 3 | Susan | Davidson | 5 |
| 5 | Safe (2012) | Action | 4 | Jenny | Adrianna | 8 |
| 5 | Safe (2012) | Action | 6 | Lee | Pong | 10 |
| 6 | Safe House(2012) | Action | 1 | Adam | Smith | 1 |
| 6 | Safe House(2012) | Action | 2 | Ravi | Kumar | 2 |
| 6 | Safe House(2012) | Action | 3 | Susan | Davidson | 5 |
| 6 | Safe House(2012) | Action | 4 | Jenny | Adrianna | 8 |
| 6 | Safe House(2012) | Action | 6 | Lee | Pong | 10 |
| 7 | GIA | 18+ | 1 | Adam | Smith | 1 |
| 7 | GIA | 18+ | 2 | Ravi | Kumar | 2 |
| 7 | GIA | 18+ | 3 | Susan | Davidson | 5 |
| 7 | GIA | 18+ | 4 | Jenny | Adrianna | 8 |
| 7 | GIA | 18+ | 6 | Lee | Pong | 10 |
| 8 | Deadline(2009) | 18+ | 1 | Adam | Smith | 1 |
| 8 | Deadline(2009) | 18+ | 2 | Ravi | Kumar | 2 |
| 8 | Deadline(2009) | 18+ | 3 | Susan | Davidson | 5 |
| 8 | Deadline(2009) | 18+ | 4 | Jenny | Adrianna | 8 |
| 8 | Deadline(2009) | 18+ | 6 | Lee | Pong | 10 |
| 9 | The Dirty Picture | 18+ | 1 | Adam | Smith | 1 |
| 9 | The Dirty Picture | 18+ | 2 | Ravi | Kumar | 2 |
| 9 | The Dirty Picture | 18+ | 3 | Susan | Davidson | 5 |
| 9 | The Dirty Picture | 18+ | 4 | Jenny | Adrianna | 8 |
| 9 | The Dirty Picture | 18+ | 6 | Lee | Pong | 10 |
| 10 | Marley and me | Romance | 1 | Adam | Smith | 1 |
| 10 | Marley and me | Romance | 2 | Ravi | Kumar | 2 |
| 10 | Marley and me | Romance | 3 | Susan | Davidson | 5 |
| 10 | Marley and me | Romance | 4 | Jenny | Adrianna | 8 |
| 10 | Marley and me | Romance | 6 | Lee | Pong | 10 |
JOINTURE INTERNE
Le JOIN interne est utilisé pour renvoyer les lignes des deux tables qui satisfont à la condition donnée.
Supposons que vous souhaitiez obtenir la liste des membres qui ont loué des films ainsi que les titres des films qu'ils ont loués. Vous pouvez simplement utiliser un INNER JOIN pour cela, qui renvoie les lignes des deux tables qui satisfont aux conditions données.
SELECT members.`first_name` , members.`last_name` , movies.`title` FROM members ,movies WHERE movies.`id` = members.`movie_id`
L'exécution du script ci-dessus donne
| first_name | last_name | title |
|---|---|---|
| Adam | Smith | ASSASSIN'S CREED: EMBERS |
| Ravi | Kumar | Real Steel(2012) |
| Susan | Davidson | Safe (2012) |
| Jenny | Adrianna | Deadline(2009) |
| Lee | Pong | Marley and me |
Notez que le script de résultats ci-dessus peut également être écrit comme suit pour obtenir les mêmes résultats.
SELECT A.`first_name` , A.`last_name` , B.`title` FROM `members`AS A INNER JOIN `movies` AS B ON B.`id` = A.`movie_id`
JOIN externes
MySQL Les JOIN externes renvoient tous les enregistrements correspondant des deux tables.
Il peut détecter les enregistrements n'ayant aucune correspondance dans la table jointe. Il revient NULL valeurs pour les enregistrements de la table jointe si aucune correspondance n’est trouvée.
Cela semble déroutant ? Regardons un exemple -
JOINDRE GAUCHE
Supposons maintenant que vous souhaitiez obtenir les titres de tous les films ainsi que les noms des membres qui les ont loués. Il est clair que certains films ne sont loués par personne. On peut simplement utiliser JOINDRE GAUCHE aux fins.
Le LEFT JOIN renvoie toutes les lignes du tableau de gauche même si aucune ligne correspondante n'a été trouvée dans le tableau de droite. Lorsqu'aucune correspondance n'a été trouvée dans le tableau de droite, NULL est renvoyé.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B ON B.`movie_id` = A.`id`
Exécuter le script ci-dessus dans MySQL workbench donne. Vous pouvez voir que dans le résultat renvoyé qui est répertorié ci-dessous pour les films qui ne sont pas loués, les champs de nom de membre ont des valeurs NULL. Cela signifie qu'aucun membre correspondant n'a trouvé de table de membres pour ce film particulier.
| title | first_name | last_name |
|---|---|---|
| ASSASSIN'S CREED: EMBERS | Adam | Smith |
| Real Steel(2012) | Ravi | Kumar |
| Safe (2012) | Susan | Davidson |
| Deadline(2009) | Jenny | Adrianna |
| Marley and me | Lee | Pong |
| Alvin and the Chipmunks | NULL | NULL |
| The Adventures of Tin Tin | NULL | NULL |
| Safe House(2012) | NULL | NULL |
| GIA | NULL | NULL |
| The Dirty Picture | NULL | NULL |
JOINDRE À DROITE
RIGHT JOIN est évidemment l’opposé de LEFT JOIN. Le RIGHT JOIN renvoie toutes les colonnes du tableau de droite même si aucune ligne correspondante n'a été trouvée dans le tableau de gauche. Lorsqu'aucune correspondance n'a été trouvée dans le tableau de gauche, NULL est renvoyé.
Dans notre exemple, supposons que vous ayez besoin d'obtenir les noms des membres et les films qu'ils ont loués. Nous avons maintenant un nouveau membre qui n'a encore loué aucun film.
SELECT A.`first_name` , A.`last_name`, B.`title` FROM `members` AS A RIGHT JOIN `movies` AS B ON B.`id` = A.`movie_id`
Exécuter le script ci-dessus dans MySQL L'établi donne les résultats suivants.
| first_name | last_name | title |
|---|---|---|
| Adam | Smith | ASSASSIN'S CREED: EMBERS |
| Ravi | Kumar | Real Steel(2012) |
| Susan | Davidson | Safe (2012) |
| Jenny | Adrianna | Deadline(2009) |
| Lee | Pong | Marley and me |
| NULL | NULL | Alvin and the Chipmunks |
| NULL | NULL | The Adventures of Tin Tin |
| NULL | NULL | Safe House(2012) |
| NULL | NULL | GIA |
| NULL | NULL | The Dirty Picture |
Clauses « ON » et « USING »
Dans les exemples de requête JOIN ci-dessus, nous avons utilisé la clause ON pour faire correspondre les enregistrements entre les tables.
La clause USING peut également être utilisée dans le même but. La différence avec EN UTILISANT est-il doit avoir des noms identiques pour les colonnes correspondantes dans les deux tables.
Jusqu’à présent, dans le tableau « films », nous avons utilisé sa clé primaire avec le nom « id ». Nous y avons fait référence dans le tableau « membres » avec le nom « movie_id ».
Renommons le champ « id » des tables « films » pour avoir le nom « movie_id ». Nous faisons cela afin d'avoir des noms de champs correspondants identiques.
ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;
Utilisons ensuite USING avec l'exemple LEFT JOIN ci-dessus.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B USING ( `movie_id` )
En plus d'utiliser ON et mes UTILISER avec les JOINs vous pouvez en utiliser beaucoup d'autres MySQL des clauses comme GROUPER PAR, OÙ et même des fonctions comme SUM, AVG, etc.
Pourquoi devrions-nous utiliser des jointures ?
Maintenant, vous vous demandez peut-être pourquoi nous utilisons les JOIN alors que nous pouvons effectuer la même tâche en exécutant des requêtes. Surtout si vous avez une certaine expérience en programmation de bases de données, vous savez que nous pouvons exécuter des requêtes une par une, utiliser la sortie de chacune dans des requêtes successives. Bien sûr, c'est possible. Mais en utilisant les JOIN, vous pouvez effectuer le travail en utilisant une seule requête avec n'importe quel paramètre de recherche. D'autre part MySQL peut obtenir de meilleures performances avec JOINs car il peut utiliser l'indexation. L'utilisation simple d'une seule requête JOIN au lieu d'exécuter plusieurs requêtes réduit la surcharge du serveur. Utiliser plutôt plusieurs requêtes qui entraînent davantage de transferts de données entre MySQL et applications (logiciels). De plus, cela nécessite également davantage de manipulations de données du côté de l'application.
Il est clair que nous pouvons faire mieux MySQL et les performances des applications grâce à l'utilisation de JOIN.
Résumé
- JOINS nous permet de combiner les données de plusieurs tables en un seul ensemble de résultats.
- JOINS ont de meilleures performances par rapport aux sous-requêtes
- INNER JOINS renvoie uniquement les lignes qui répondent aux critères donnés.
- OUTER JOINS peut également renvoyer des lignes pour lesquelles aucune correspondance n'a été trouvée. Les lignes sans correspondance sont renvoyées avec le mot clé NULL.
- Les principaux types de JOIN incluent Inner, Left Outer, Right Outer, Cross JOINS, etc.
- La clause fréquemment utilisée dans les opérations JOIN est « ON ». La clause « USING » exige que les colonnes correspondantes portent le même nom.
- JOINS peut également être utilisé dans d'autres clauses telles que GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS, etc.