Quelles sont les méthodes d’implémentation des jointures internes MySQL, des jointures externes et des JOINS SQL ?

1. Jointure interne

Jointure interne : fusionner les lignes de plus de deux tables avec la même colonne. L'ensemble de résultats ne contient pas de lignes d'une table qui correspondent à une autre table ne contient pas .

En termes humains, les résultats de la requête contiennent uniquement les lignes auxquelles ils correspondent, et celles qui ne correspondent pas sont ignorées.

[Exemple] Recherchez le numéro d'employé employee_id et son nom de service correspondant department_name. Le nom du département department_name se trouve uniquement dans la table des départements departments, et la table des départements departments est comme indiqué dans la figure ci-dessous :

employee_id 和其对应的部门名称 department_name 。其中部门名称 department_name 只在部门表 departments 中，部门表 departments 如下图所示：

员工表 employees 和部门表 departments 通过部门编号 department_id 匹配连接起来。查询代码如下所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`;

查询结果：

这里返回了 106 条记录，但员工表 employees 总共是有107条记录的，还少了 1 个人。原因是在员工表 employees 中，有一个员工的部门编号 department_id 为 (NULL) ，如下图所示：

而部门表 departments 中却没有值为 (NULL) 的部门编号 department_id ，因此这一行不匹配的数据就被丢弃不显示了。如下图所示，内连接只包含两个表匹配的行，即下图中两圆相交的部分：

这种连接方式称作内连接。

2.外连接

外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行。

外连接又分为以下三类：

左外连接：

两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行。如下图中，左外连接就是左边一整个圆。

右外连接：

两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。如下图中，右外连接就是右边一整个圆。

满外连接：

在表连接过程中，除了返回符合连接条件的行之外，还会返回左表和右表中不符合条件的行。如下图中，满外连接就是两个圆所有部分。

【例子】根据部门编号 department_id ，查询员工表 employees 中的所有员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

【分析】凡是题目中出现要求查询 所有 的字眼时，都要打起十二分精神，这说明需要我们使用外连接查询。实现外连接可使用SQL92和SQL99两种语法，详见[5.9 常用的SQL标准](# 5.9 常用的SQL标准) 。由于左表员工表 employees 共有 107 条数据，而右表和左表匹配的数据仅有106条，需要使用左外连接。

【SQL92语法实现外连接】使用 (+)

La table des employésemployees et la table des départements <code>departments sont jointes en faisant correspondre le numéro de département department_id. Le code de la requête est le suivant :

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`(+);

Résultats de la requête :

106 enregistrements sont renvoyés ici, mais la table des employés employees contient un total de 107 enregistrements et une personne est manquante. La raison en est que dans la table des employés employees, il y a un employé dont le numéro de service department_id est (NULL), comme le montre la figure suivante :

Et le table des départements departmentsdepartment_id avec une valeur de (NULL) dans /code>, donc les données sans correspondance dans cette ligne sont ignorées et ne sont pas affichées. Comme le montre la figure ci-dessous, la jointure interne ne contient que les lignes correspondantes des deux tables, c'est-à-dire l'intersection des deux cercles dans la figure ci-dessous :

🎜🎜🎜🎜🎜Ce type de connexion est appelé jointure interne. 🎜🎜2. Jointure externe🎜🎜Jointure externe : fusionnez les lignes de plus de deux tables avec la même colonne en plus des lignes correspondant à une table et à une autre table, l'ensemble de résultats interroge également la table de gauche ou la table de droite. lignes. 🎜🎜Les jointures externes sont divisées en trois catégories suivantes : 🎜

Jointure externe gauche :

🎜Dans le processus de jointure de deux tables, en plus de renvoyer des lignes qui remplissent les conditions de connexion, elles renvoient également 🎜gauche🎜 conditions qui ne remplissent pas les conditions OK. Comme le montre l'image ci-dessous, la jointure externe gauche correspond au cercle entier de gauche. 🎜🎜🎜

Jointure externe droite :

🎜En plus de renvoyer les lignes qui remplissent les conditions de connexion entre les deux tables pendant le processus de connexion, elle renvoie également les lignes qui ne remplissent pas les conditions de la table 🎜right🎜 . Comme le montre l'image ci-dessous, la jointure extérieure droite correspond au cercle entier de droite. 🎜🎜🎜

Jointure externe complète :

🎜Pendant le processus de connexion de la table, en plus de renvoyer les lignes qui remplissent les conditions de connexion, les lignes qui ne remplissent pas les conditions des tables de gauche et de droite seront également renvoyées. Comme le montre la figure ci-dessous, la jointure externe complète correspond à toutes les parties des deux cercles. 🎜🎜🎜🎜[Exemple] En fonction du numéro de service department_id, interrogez 🎜tous🎜les numéros d'employé employee_id et la table du département dans la table des employés <code>employés Le nom du département correspondant <code>department_name dans les départements. 🎜🎜【Analyse】 Chaque fois que les mots demandant d'interroger tout apparaissent dans la question, nous devons être très vigilants. Cela signifie que nous devons utiliser des jointures externes pour interroger. Les syntaxes SQL92 et SQL99 peuvent être utilisées pour implémenter des jointures externes. Pour plus de détails, voir [5.9 Normes SQL couramment utilisées] (# 5.9 Normes SQL couramment utilisées). Étant donné que la table des employés de gauche employés contient un total de 107 éléments de données et que la table de droite et la table de gauche ne correspondent qu'à 106 éléments de données, une jointure externe gauche doit être utilisée. 🎜🎜[Syntaxe SQL92 pour implémenter les jointures externes] Utilisez (+). 🎜

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

🎜Résultat de la requête : Erreur 🎜🎜🎜🎜🎜Cela est dû au fait que MySQL ne prend pas en charge l'opération de jointure externe de la syntaxe SQL92. Mais Oracle le prend en charge. Il n’y a donc pas d’apprentissage en vain. MySQL ne prend en charge que la syntaxe SQL99 pour implémenter des requêtes multi-tables. 🎜 🎜🎜3. La syntaxe SQL99 implémente une requête multi-table🎜🎜SQL99 fait référence à la spécification standard de syntaxe SQL publiée par SQL en 1999. Bien qu'une série de nouveaux standards SQL aient été publiés plus tard, dans le processus d'apprentissage de MySQL, il suffit de maîtriser principalement SQL99 et SQL92. À partir de cette section, l'étude de MySQL est à mi-chemin, car avant cette section était la syntaxe SQL92, à partir de cette section est dédiée à la syntaxe SQL99. 🎜

SQL99语法使用 JOIN...ON 的方式实现多表查询，且可以同时实现内连接和三种外连接。MySQL是支持这种方式的。

3.1 SQL99实现内连接

【例子：三表查询】查询员工的员工编号 employee_id 、 姓名 last_name 、部门名称 department_name 和所在城市 city 。

【分析】这个需求需要 3 张表共同查询。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

SQL99语法就是加一张表，就 JOIN 一张表，并在 ON 后加连接条件。注意，这里的 JOIN 前面还省略了表示内连接的关键字 INNER ，在使用内连接时可以忽略。即代码还可以写成完整形式：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp INNER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

查询结果：

3.2 SQL99语法实现外连接

3.2.1 左外连接

【例子】根据部门编号 department_id ，查询员工表 employees 中的所有员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

【分析】由于左表是员工表 employees ，有107条数据；而右表是部门表 departments ，有27条数据。题目要求是返回所有员工的107条查询结果，因此这里使用左外连接。SQL99实现左连接接很简单，只需要在 JOIN 前加上两个关键字 LEFT OUTER 即可表示左外连接。如下代码所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

其中，OUTER 可以省略，即写成：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查询结果：

3.2.2 右外连接

举一反三地，右外连接就是在 OUTER JOIN 前加一个关键字 RIGHT 。

SELECT emp.`employeed/master/img/d`;

查询结果：

查询结果有122条记录，这怎么解释呢？再回想一下右外连接的定义：

两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。如下图中，右外连接就是右边一整个圆。

就不难理解，因为右表部是没有人的。而左、右表匹配的数据有106条 (两圆相交部分) ，因此一共就有 106 + 16 = 122 106+16=122 106+16=122 条记录。如下图所示：

这个例子能更好地帮助我们理解右外连接。

3.2.3 满外连接

举一反三地，满外连接就是在 OUTER JOIN 前加一个关键字 FULL 。但很不幸，MySQL不支持SQL99的满外连接语法，Oracle是支持的。

我们需要使用别的方法实现MySQL中的满外连接，详见4.6 满外连接 。

4.总结：七种SQL JOINS的实现

在开始本节之前，需要您了解SQL的 UNION 和 UNION ALL 的定义和实现。如果需要了解，可以阅读这篇博文：《MySQL中 UNION 并的使用》。

4.1 内连接

根据部门编号 department_id ，查询员工表 employees 中的员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查询结果：

4.2 左外连接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查询结果：

4.3 右外连接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

4.4 第四种JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查询结果：

作用是把员工表 employees 中，部门编号 department_id 为 (NULL) 的那一个员工查询出来了，如下图所示：

4.5 第五种JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, emp.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查询结果：

4.6 满外连接

由于MySQL不支持SQL99语法的满外连接。因此，我们的实现方式就是求

4.2 左外连接 和 4.5 第五种JOIN 的并 UNION ALL 即可；或者求4.3 右外连接 和 4.4 第四种JOIN 的并 UNION ALL 也行，都是一样的效果。

方法一

方法二

# 方法一
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

# 方法二
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查询结果：

4.7 第七种JOIN

实现下面这个操作只需要把 4.4 第四种JOIN 和 4.5 第五种JOIN 求 UNION ALL 即可。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查询结果：

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!