Explication détaillée de Mysql sur JOIN

Tutoriel vidéo MySQLExplication détaillée de la colonneRejoindre

Recommandations d'apprentissage gratuites associées : Tutoriel vidéo MySQL

Indice 0

• JOINOrdre d'exécution des déclarations
• INNER/LEFT/RIGHT/FULL JOINLa différence entre
• ON et WHERE

1 Présentation

Un SQL complet L'instruction sera divisé en plusieurs clauses. Lors de l'exécution des clauses, une table virtuelle (vt) sera générée, mais le résultat ne renverra que la dernière table virtuelle. À partir de cette idée, nous essayons de comprendre le processus d’exécution de la requête JOIN et de répondre à quelques questions courantes.
Si vous n'avez aucune idée des résultats d'exécution des différents JOIN auparavant, vous pouvez lire cet article ci-dessous

2 Séquence d'exécution JOIN

Ce qui suit est la structure générale de la requête JOIN

SELECT <row_list> 
  FROM <left_table> 
    <inner|left|right> JOIN <right_table> 
      ON <join condition> 
        WHERE <where_condition>

Son ordre d'exécution est le suivant (La première exécutée dans l'instruction SQL est toujours la clause FROM)  :

• FROM : Droite et gauche Le produit cartésien des deux tableaux est effectué pour produire le premier tableau vt1. Le nombre de lignes est n*m (n est le nombre de lignes dans le tableau de gauche, m est le nombre de lignes dans le tableau de droite
• ON : Filtrer vt1 ligne par ligne selon à la condition ON, et insérez le résultat dans vt2
• JOIN : Ajouter une ligne externe si LEFT JOIN( LEFT OUTER JOIN), puis parcourez d'abord chaque ligne de table de gauche , et les lignes qui ne sont pas dans vt2 seront insérées dans vt2, et les champs restants de la ligne seront remplis avec NULL, formant vt3 ; il en va de même si RIGHT JOIN est spécifié, mais si INNER JOIN est spécifié, il n'ajoutera pas de lignes externes, le processus d'insertion ci-dessus est ignoré, vt2=vt3 (donc la condition de filtre de INNER JOIN est placée dans ON ou OÙIl n'y a aucune différence dans les résultats d'exécution, qui seront expliqués en détail ci-dessous)
• OÙ : effectuez un filtrage conditionnel sur vt3 et les lignes qui remplissent les conditions sont affichées. Vers vt4
• SELECT : supprimez les champs spécifiés de vt4 à vt5

Utilisons un exemple pour présenter le processus ci-dessus de jointure de tables (cet exemple n'est pas une bonne pratique, juste pour illustrer la syntaxe de jointure)

3 Exemple

Créer un tableau d'informations utilisateur :

CREATE TABLE `user_info` (
  `userid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Créer un tableau de solde utilisateur :

CREATE TABLE `user_account` (
  `userid` int(11) NOT NULL,
  `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Peu importe Importer des données :

select * from user_info;
+--------+------+
| userid | name |
+--------+------+
|   1001 | x    |
|   1002 | y    |
|   1003 | z    |
|   1004 | a    |
|   1005 | b    |
|   1006 | c    |
|   1007 | d    |
|   1008 | e    |
+--------+------+
8 rows in set (0.00 sec)

select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |
+--------+-------+
4 rows in set (0.00 sec)

Au total, 8 utilisateurs ont des noms d'utilisateur, et 4 utilisateurs ont soldes dans leurs comptes.
Obtenez le nom d'utilisateur et le solde avec l'ID utilisateur 1003. Le SQL est le suivant :

SELECT i.name, a.money 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON i.userid = a.userid 
        WHERE a.userid = 1003;

Étape 1 : Exécutez la clause FROM pour effectuer une opération de produit cartésien. sur les deux tables

Après l'opération du produit cartésien, la combinaison de toutes les lignes des deux tables sera renvoyée. La table de gauche user_info a 8 lignes. La table de droite user_account a 4 lignes et la table virtuelle générée. vt1 vaut 8*4=32 lignes :

SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1001 |    22 |
|   1003 | z    |   1001 |    22 |
|   1004 | a    |   1001 |    22 |
|   1005 | b    |   1001 |    22 |
|   1006 | c    |   1001 |    22 |
|   1007 | d    |   1001 |    22 |
|   1008 | e    |   1001 |    22 |
|   1001 | x    |   1002 |    30 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1002 |    30 |
|   1004 | a    |   1002 |    30 |
|   1005 | b    |   1002 |    30 |
|   1006 | c    |   1002 |    30 |
|   1007 | d    |   1002 |    30 |
|   1008 | e    |   1002 |    30 |
|   1001 | x    |   1003 |     8 |
|   1002 | y    |   1003 |     8 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   1003 |     8 |
|   1005 | b    |   1003 |     8 |
|   1006 | c    |   1003 |     8 |
|   1007 | d    |   1003 |     8 |
|   1008 | e    |   1003 |     8 |
|   1001 | x    |   1009 |    11 |
|   1002 | y    |   1009 |    11 |
|   1003 | z    |   1009 |    11 |
|   1004 | a    |   1009 |    11 |
|   1005 | b    |   1009 |    11 |
|   1006 | c    |   1009 |    11 |
|   1007 | d    |   1009 |    11 |
|   1008 | e    |   1009 |    11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)

Étape 2 : Exécuter la clause ON pour filtrer les lignes qui ne remplissent pas les conditions

ON i .userid = a.userid Après le filtrage, vt2 est le suivant :

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Étape 3 : JOIN pour ajouter des lignes externes

LEFT JOIN ​​​​​​ajoutera des lignes du tableau de gauche qui n'apparaissent pas dans vt2 Insérer dans vt2, les champs restants de chaque ligne seront remplis avec NULL, RIGHT JOINIdentique à
Dans cet exemple, LEFT JOIN ​​​​​​est utilisé, donc la table de gauche sera be user_infoAjoutez les lignes restantes pour générer la table vt3 :

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

Étape 4 : WHERE filtrage des conditions

WHERE a.userid = 1003 Générer la table vt4 :

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Étape 5 : SELECT

SELECT i.name, a.money Générer vt5 :

+------+-------+
| name | money |
+------+-------+
| z    |     8 |
+------+-------+

La table virtuelle vt5 est renvoyée au client comme résultat final

Introduction vers le lien complet Après le processus de table, examinons les différences entre JOIN

4 couramment utilisé et les différences entre INNER/LEFT/RIGHT/FULL JOIN

• INNER JOIN. ..ON... : renvoie toutes les lignes qui correspondent dans les tableaux de gauche et de droite (car seule la deuxième étape du filtrage ON ci-dessus est effectuée, et la troisième étape d'ajout les lignes externes ne sont pas effectuées)
• LEFT JOIN...ON... : renvoie toutes les lignes du tableau de gauche. Si certaines lignes n'ont pas de lignes correspondantes dans le tableau de droite, définissez les colonnes du tableau de droite sur NULL
dans le nouveau tableau
• RIGHT JOIN...ON... : renvoie toutes les lignes du tableau de droite. lignes correspondantes correspondantes dans le tableau de gauche, ajoutez les colonnes du tableau de gauche au nouveau tableau. La position centrale est NULL

INNER JOIN

Faites la troisième étape ci-dessus . pour ajouter une ligne externe à titre d'exemple, si LEFT JOIN ​​​​est remplacé par INNER JOIN, cette étape sera ignorée, et la table générée vt3 sera exactement la identique à vt2 :

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

RIGHT JOIN

Si LEFT JOIN ​​​​​​est remplacé devient RIGHT JOIN, alors la table générée vt3 est la suivante :

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
+--------+------+--------+-------+

Parce que la ligne userid=1009 existe dans user_account (table de droite), mais elle n'est pas trouvée dans user_info (table de gauche) Il n'y a aucun enregistrement pour cette ligne, donc la ligne suivante sera inséré dans la troisième étape :

|   NULL | NULL |   1009 |    11 |

FULL JOIN

上文引用的文章中提到了标准SQL定义的FULL JOIN，这在mysql里是不支持的，不过我们可以通过LEFT JOIN + UNION + RIGHT JOIN 来实现FULL JOIN：

SELECT * 
  FROM user_info as i 
    RIGHT JOIN user_account as a 
      ON a.userid=i.userid
union 
SELECT * 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON a.userid=i.userid;

他会返回如下结果：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

ps：其实我们从语义上就能看出LEFT JOIN和RIGHT JOIN没什么差别，两者的结果差异取决于左右表的放置顺序，以下内容摘自mysql官方文档：

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

所以当你纠结使用LEFT JOIN还是RIGHT JOIN时，尽可能只使用LEFT JOIN吧

5 ON和WHERE的区别

上文把JOIN的执行顺序了解清楚之后，ON和WHERE的区别也就很好理解了。
举例说明:

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

第一种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid and i.userid = 1003的行，生成表vt2，然后执行第三步JOIN子句，将外部行添加进虚拟表生成vt3即最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   NULL |  NULL |
|   1002 | y    |   NULL |  NULL |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

而第二种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid的行，生成表vt2；再执行第三步JOIN子句添加外部行生成表vt3；然后执行第四步WHERE子句，再对vt3表进行过滤生成vt4，得的最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+
vt4:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

如果将上例的LEFT JOIN替换成INNER JOIN，不论将条件过滤放到ON还是WHERE里，结果都是一样的，因为INNER JOIN不会执行第三步添加外部行

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

返回结果都是:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

想了解更多编程学习，敬请关注php培训栏目！

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!