Instructions de requête conjointe multi-tables MySQL

1. Types de connexion multi-tables
1. Le produit cartésien (jointure croisée) dans MySQL peut être CROSS JOIN ou omettre CROSS, qui est JOIN, ou utiliser ',' tel que :

Parce que il renvoie Le résultat est le produit des deux tables de données connectées, il n'est donc généralement pas recommandé de l'utiliser lorsqu'il y a des conditions WHERE, ON ou USING, car lorsqu'il y a trop d'éléments de la table de données, cela sera très lent. Utilisez généralement LEFT [OUTER] JOIN ou RIGHT [OUTER] JOIN

2. INNER JOIN INNER JOIN est appelé une équijoin dans MySQL, c'est-à-dire que vous devez spécifier les conditions d'équijoin dans CROSS et INNER JOIN dans MySQL divisé ensemble. join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

SELECT * FROM table1 CROSS JOIN table2 
SELECT * FROM table1 JOIN table2 
SELECT * FROM table1,table2

3. Les jointures externes dans MySQL sont divisées en jointures externes gauche et jointures droites, c'est-à-dire en plus de renvoyer des résultats qui remplir les conditions de jointure De plus, les résultats qui ne remplissent pas les conditions de jointure dans la table de gauche (jointure gauche) ou de droite (jointure droite) doivent être renvoyés et NULL est utilisé en conséquence.

Exemple :

table utilisateur :

id | name
———
1 | libk
2 | zyfon
3 | daodao

table user_action :

user_id | action
—————
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql :

select id, name, action from user as u
left join user_action a on u.id = a.user_id

résultat :

id | name  | action
——————————–
1 | libk     | jump      ①
1 | libk     | kick       ②
1 | libk     | jump      ③
2 | zyfon   | run        ④
3 | daodao | null       ⑤

Analyse :
Notez qu'il existe un enregistrement de user_id=4, action=swim dans user_action, mais il n'apparaît pas dans le résultat.,
L'utilisateur avec id=3 et name=daodao dans la table user n'a pas d'enregistrement correspondant dans user_action, mais il apparaît dans l'ensemble de résultats
Parce qu'il s'agit d'une jointure gauche maintenant, tout fonctionne est basé sur la gauche.
Les résultats 1, 2, 3 et 4 sont tous des enregistrements dans la table de gauche et dans la table de droite. 5 est un enregistrement uniquement dans la table de gauche mais pas dans la table de droite

Principe de fonctionnement :

Lisez-en un dans le tableau de gauche, sélectionnez tous les enregistrements du tableau de droite (n) qui correspondent et connectez-les pour former n enregistrements (y compris les lignes répétées, telles que le résultat 1 et résultat 3), S'il n'y a pas de table à droite qui correspond à la condition on, alors les champs connectés sont nuls. Continuez ensuite à lire l'élément suivant.

Extension :

Nous pouvons utiliser la règle selon laquelle s'il n'y a pas de correspondance dans la table de droite, null sera affiché pour trouver tous les enregistrements qui sont dans la table de gauche mais pas dans la table de droite. Notez que la colonne utilisée pour le jugement doit être déclarée non nulle.
Par exemple :
sql:

select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL

(Remarque :

1. Si la valeur de la colonne est nulle, vous devez utiliser is null à la place of =NULL

  2. La colonne a.user_id ici doit être déclarée comme NON NULL.

)

Le résultat du sql ci-dessus :

id | name | action
————————–
3 | daodao | NULL
 
——————————————————————————–

Utilisation générale :

a. LEFT [OUTER] JOIN :

En plus de renvoyer des résultats qui répondent aux conditions de jointure, vous devez également afficher les colonnes de données dans le fichier. table de gauche qui ne remplit pas les conditions de jointure. Utilisez NULL en conséquence. Correspond à

SELECT nom_colonne FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column

b. :

RIGHT est similaire à LEFT JOIN uniquement. En plus d'afficher les résultats qui remplissent les conditions de connexion, il est également nécessaire d'afficher les colonnes de données dans le tableau de droite qui ne remplissent pas les conditions de connexion. en conséquence

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column= table2.column

Conseils :

1 sur a.c1 = b.c1 équivaut à utiliser. (c1)

2. INNER JOIN et, (virgule) sont sémantiquement équivalents
3 Lorsque MySQL récupère des informations d'une table, vous pouvez lui demander de choisir quel index.
Cette fonctionnalité peut être utile si EXPLAIN montre que MySQL utilise le mauvais index dans la liste des index possibles.
En spécifiant USE INDEX (key_list), vous pouvez demander à MySQL d'utiliser le plus approprié des index possibles pour trouver des lignes dans la table.
La syntaxe facultative de deuxième choix IGNORE INDEX (key_list) peut être utilisée pour indiquer à MySQL de ne pas utiliser un index spécifique. Par exemple :

mysql> SELECT * FROM table1 USE INDEX (key1,key2)
-> WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
-> WHERE key1=1 AND key2=2 AND key3=3;

2. Contraintes de connexion à la table

Ajouter des conditions d'affichage WHERE, ON, USING

1 clause WHERE

mysql>
SELECT * FROM table1,table2 WHERE table1.id=table2.id;

2 . ON

mysql>
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;

3. Clause USING, si les deux colonnes de la condition de jointure des deux tables ont le même nom, vous pouvez utiliser USING

Par exemple. :

SELECT FROM LEFT JOIN USING ()

Exemples de connexion de plus de deux tables :

mysql>
 
SELECT artists.Artist, cds.title, genres.genre 
  
FROM cds 
  
LEFT JOIN genres N cds.genreID = genres.genreID 
  
LEFT JOIN artists ON cds.artistID = artists.artistID;

ou

mysql>
 
SELECT artists.Artist, cds.title, genres.genre 
  
FROM cds 
  
LEFT JOIN genres ON cds.genreID = genres.genreID 
  
 LEFT JOIN artists -> ON cds.artistID = artists.artistID
  
 WHERE (genres.genre = 'Pop');

Autres choses à noter Quand il s'agit aux requêtes multi-tables dans MySQL, vous devez décider quelle méthode de connexion est la plus efficace en fonction de la situation de la requête.

1. Jointure croisée (produit cartésien) ou jointure interne [INNER | CROSS] JOIN

2. Jointure externe gauche LEFT [OUTER] JOIN ou jointure externe droite RIGHT [OUTER] JOIN Remarque Spécifiez les conditions de connexion WHERE, ON, USING.

PS : Utilisation de base de JOIN

Nous supposons d'abord qu'il y a 2 tables A et B. Leurs structures de table et leurs champs sont :

Tableau A :

ID Nom

1 Tim
2 Jimmy
3 John
4 Tom

Tableau B :

ID Hobby
1 Football
2 Basket
2 Tennis
4 Football

1. Jointure interne :

Select A.Name, B.Hobby from A, B where A.id = B.id

Il s'agit d'une jointure interne implicite. Le résultat de la requête est :

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

Sa fonction est la même que

Select A.Name from A INNER JOIN B ON A.id = B.id

是一样的。这里的INNER JOIN换成CROSS JOIN也是可以的。

2．  外左联结

Select A.Name from A Left JOIN B ON A.id = B.id

 典型的外左联结，这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：

Name Hobby
Tim Football
Jimmy Basketball，Tennis
John
Tom Soccer

所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。
3．  外右联结
如果把上面查询改成外右联结：

Select A.Name from A Right JOIN B ON A.id = B.id

则结果将会是：

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

这样的结果都是我们可以从外左联结的结果中猜到的了。

说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？最后给大家讲讲MySQL联结查询中的某些参数的作用：
1．USING (column_list)：其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：

a LEFT JOIN b USING (c1,c2,c3)

其作用相当于下面语句

a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

只是用ON来代替会书写比较麻烦而已。

2．NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有字段的Left JOIN（左联结）。

3．STRAIGHT_JOIN：由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。

最后要说的就是，在MySQL5.0以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结，因此你输入了下面的联结查询：

SELECT t1.id,t2.id,t3.id
FROM t1,t2
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

但是MySQL并不是这样执行的，其后台的真正执行方式是下面的语句：

SELECT t1.id,t2.id,t3.id
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) )
WHERE t1.id=t2.id;

这并不是我们想要的效果，所以我们需要这样输入：

SELECT t1.id,t2.id,t3.id
FROM (t1,t2)
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

MySQL联合查询效率较高，以下例子来说明联合查询（内联、左联、右联、全联）的好处：

T1表结构（用户名,密码）   
userid（int）   usernamevarchar（20）   passwordvarchar（20）   
1   jack  jackpwd   
2   owen  owenpwd   


T2表结构（用户名,密码）   
userid（int）   jifenvarchar（20）   dengjivarchar（20）   
    1   20   3   
    3   50   6   


第一：内联（inner join）
如果想把用户信息、积分、等级都列出来，那么一般会这样写：

select * from T1, T3 where T1.userid = T3.userid
（其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid ）。

把两个表中都存在userid的行拼成一行（即内联），但后者的效率会比前者高很多，建议用后者（内联）的写法。

SQL语句：
select * from T1 inner join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   

第二：左联（left outer join）
显示左表T1中的所有行，并把右表T2中符合条件加到左表T1中；
右表T2中不符合条件，就不用加入结果表中，并且NULL表示。

SQL语句：
select * from T1 left outer join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   
2   owen   owenpwd   NULL   NULL   NULL   

第三：右联（right outer join）。

Affichez toutes les lignes du tableau de droite T2, et ajoutez les conditions correspondantes du tableau de gauche T1 au tableau de droite T2
Si les conditions du tableau de gauche T1 ne sont pas remplies, il n'est pas nécessaire d'en ajouter ; dans la table des résultats, et NULL est indiqué.

Instruction SQL :
sélectionnez * à partir de T1, jointure externe droite T2 sur T1.userid = T2.userid

Résultats en cours d'exécution
T1.userid nom d'utilisateur mot de passe T2.userid jifen dengji
1 jack jackpwd 1 20 3
NULL NULL NULL 3 50 6

Quatrième : jointure complète externe

Affichez toutes les lignes du tableau de gauche T1 et du tableau de droite T2, c'est-à-dire combinez ensemble le tableau de résultats joint à gauche et le tableau de résultats joint à droite, puis filtrez les doublons.

Instruction SQL :
select * from T1 full external join T2 on T1.userid = T2.userid

Exécution des résultats
T1.userid nom d'utilisateur mot de passe T2.userid jifen dengji
1 jack jackpwd 1 20 3
2 owen owenpwd NULL NULL NULL
NULL NULL NULL 3 50 6

En résumé, concernant les requêtes conjointes, l'efficacité est en effet relativement high, 4 Si cette méthode de combinaison peut être utilisée de manière flexible, les structures d'instructions fondamentalement complexes deviendront plus simples.