Maison >base de données >tutoriel mysql >Quelles sont les méthodes d'implémentation des jointures internes MySQL, des jointures externes et des JOINS SQL ?
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
,因此这一行不匹配的数据就被丢弃不显示了。如下图所示,内连接只包含两个表匹配的行,即下图中两圆相交的部分:
这种连接方式称作内连接。
外连接:合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行之外,还查询到了左表或右表中不匹配的行。
外连接又分为以下三类:
两个表在连接过程中除了返回满足连接条件的行以外,还返回左表中不满足条件的行。如下图中,左外连接就是左边一整个圆。
两个表在连接过程中除了返回满足连接条件的行以外,还返回右表中不满足条件的行。如下图中,右外连接就是右边一整个圆。
在表连接过程中,除了返回符合连接条件的行之外,还会返回左表和右表中不符合条件的行。如下图中,满外连接就是两个圆所有部分。
【例子】根据部门编号 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 :
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是支持这种方式的。
【例子:三表查询】查询员工的员工编号 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`;
查询结果:
【例子】根据部门编号 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`;
查询结果:
举一反三地,右外连接就是在 OUTER JOIN
前加一个关键字 RIGHT
。
SELECT emp.`employeed/master/img/d`;
查询结果:
查询结果有122条记录,这怎么解释呢?再回想一下右外连接的定义:
两个表在连接过程中除了返回满足连接条件的行以外,还返回右表中不满足条件的行。如下图中,右外连接就是右边一整个圆。
就不难理解,因为右表部是没有人的。而左、右表匹配的数据有106条 (两圆相交部分) ,因此一共就有 106 + 16 = 122 106+16=122 106+16=122 条记录。如下图所示:
这个例子能更好地帮助我们理解右外连接。
举一反三地,满外连接就是在 OUTER JOIN
前加一个关键字 FULL
。但很不幸,MySQL不支持SQL99的满外连接语法,Oracle是支持的。
我们需要使用别的方法实现MySQL中的满外连接,详见4.6 满外连接 。
在开始本节之前,需要您了解SQL的 UNION
和 UNION ALL
的定义和实现。如果需要了解,可以阅读这篇博文:《MySQL中 UNION 并的使用》。
根据部门编号 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`;
查询结果:
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`;
查询结果:
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`;
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) 的那一个员工查询出来了,如下图所示:
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;
查询结果:
由于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.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!