09.MySQL表里连接

09.MySQL表里连接

文章目录

MySQL表里连接
内连接
外连接
左外连接
右外连接
简单案例

---

MySQL表里连接

在数据库操纵中，表的连接是一个非常重要的概念。简单来说，连接就是将两个或多个表中的数据按照某种规则结合起来，从而获取我们所需要的信息。而在实际开发中，最常用的两种连接方式是内连接和外连接。
那么，这两种连接到底有什么区别呢？又分别适用于哪些场景呢？
内连接

先从内连接提及。顾名思义，内连接（INNER JOIN）就是只返回两个表中满足连接条件的数据。也就是说，只有当左表和右表都存在匹配的数据时，才会出现在结果会集。
那具体怎么用呢？其实语法也不复杂：

1. SELECT ... FROM t1 INNER JOIN t2 ON 连接条件 [INNER JOIN t3 ON 连接条件] ... AND 其他条件;

复制代码

其中，大写的 SELECT、FROM、INNER JOIN、ON 等都是 SQL 的关键字；而 [ ] 中的内容则是可选的，好比你可以根据需要添加更多的连接条件或者其他筛选条件。
举个例子吧，假设我们现在有两张表：一张员工表，记载了员工的基本信息，另一张部分表，记载了部分的编号和名称。现在我们要查询员工 “SMITH” 的名字和他的部分名称。
按照复合查询的做法，我们大概会先对两张表做笛卡尔积，然后在 WHERE 子句中指定筛选条件：员工的部分编号即是部分表的部分编号，而且员工姓名是 “SMITH”。但其实，这种写法本质上就是内连接，只不外标准的写法更直观一些：

1. SELECT ename, dname
2. FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
3. AND ename = 'SMITH';

复制代码

如许写的利益是逻辑更清楚，也更轻易维护。
外连接

说完内连接，咱们再来看看外连接。外连接和内连接最大的区别在于：外连接可以返回不满足连接条件的行，而内连接不行。
外连接又分为左外连接（LEFT JOIN）和右外连接（RIGHT JOIN）。简单来说，左外连接会返回左表中的所有行，即使右表中没有匹配的数据；而右外连接则相反，会返回右表中的所有行。
左外连接

假设我们现在有一张学生表和一张结果表。学生表记载了学生的学号和姓名，结果表记载了学生的学号和考试结果。现在有个需求：查询所有学生的结果，即使这个学生没有结果，也要显示他的个人信息。
如果直接用内连接的话，那些没有结果的学生信息就查不出来。这时候就得用左外连接了：

1. SELECT student.name, score.score
2. FROM student LEFT JOIN score ON student.id = score.id;

复制代码

如许，即使某个学生的学号在结果表中找不到匹配记载，他的姓名依然会出现在结果中，而对应的结果字段则会显示为 NULL。
右外连接

右外连接的利用场景和左外连接类似，只不外方向相反。好比，如果我们想查询所有的结果记载，即使某个结果对应的学号在学生表中不存在，也可以用右外连接：

1. SELECT student.name, score.score
2. FROM student RIGHT JOIN score ON student.id = score.id;

复制代码

这时候，结果表中的所有记载都会被生存，而学生表中没有匹配的字段会显示为 NULL。
简单案例

为了更好地理解这些连接方式的区别，咱们再来看一个实际案例。
需求：列出所有部分的名称，以及这些部分的员工信息（包括没有员工的部分）。
这里的关键是：即使某个部分没有员工，也要显示出来。这时候，内连接显然不够用了，因为内连接只会显示满足连接条件的记载。以是，我们需要用外连接。
假设部分表在左边，员工表在右边，我们可以如许写：

1. SELECT dept.dname, emp.ename
2. FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;

复制代码

如许，所有部分名称都会被列出，而没有员工的部分对应的 ename 字段会是 NULL。
固然，如果你想用右外连接实现同样的效果，也可以把部分表放在右边：

1. SELECT dept.dname, emp.ename
2. FROM emp RIGHT JOIN dept ON dept.deptno = emp.deptno;

复制代码

结果是一样的，只是写法不同罢了。

---

内连接

内连接的核心头脑是“只生存有交集的数据”。换句话说，如果一张表的某条记载在另一张表中找不到匹配项，这条记载就不会出现在最终结果里。
举个生活中的例子：假设你有一个朋友列表，还有一个聚会的签到表。如果你想查哪些朋友到场了聚会，就可以用内连接，把朋友列表和签到表按名字关联起来。如许，结果里只会包罗既在朋友列表里、又在签到表里的名字。
再回到数据库层面。内连接的语法结构其实挺固定的，核心就是 INNER JOIN ... ON ...。好比，如果我们想查询每个员工的姓名和他们地点部分的名称，就可以如许写：

1. SELECT emp.ename, dept.dname
2. FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

复制代码

这里的关键是 ON emp.deptno = dept.deptno，它指定了两张表的关联条件。只有当员工的部分编号和部分表的部分编号同等时，这两条记载才会被合并成一条结果。
不外，有时候我们还需要加一些额外的筛选条件。好比，只想查某个特定部分的员工信息，这时候就可以在 AND 后面加条件：

1. SELECT emp.ename, dept.dname
2. FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
3. AND dept.dname = 'SALES';

复制代码

如许，结果就只会包罗销售部的员工了。
内连接的本质

从底层原理来看，内连接其实就是对两个表进行笛卡尔积之后，再通过连接条件过滤出有效的组合。好比，如果员工表有 10 条记载，部分表有 5 条记载，它们的笛卡尔积就是 50 条记载。然后，数据库会根据 emp.deptno = dept.deptno 这个条件筛选出符合条件的数据。
不外，虽然内连接的逻辑简单，但在实际开发中一定要注意连接条件的准确性。如果连接条件写错了，好比把 emp.deptno = dept.deptno 错写成 emp.deptno = dept.loc，那结果就会完全错误，以致大概导致性能问题。
内连接的优化

在大数据量场景下，内连接的性能优化也很重要。好比：

• 尽量在连接条件上利用索引：如果 emp.deptno 或 dept.deptno 上有索引，数据库的查询服从会高很多。
  
• 克制不须要的字段到场连接：好比，如果只需要查询员工姓名和部分名称，就不要把整个员工表和部分表的所有字段都选出来。
  
• 公道利用子查询：有时候，先通过子查询过滤数据，再做内连接，效果会更好。
  

举个例子，假设我们想查薪资高于平均值的员工信息，可以如许写：

1. SELECT emp.ename, emp.sal
2. FROM emp INNER JOIN (
3.     SELECT AVG(sal) AS avg_sal FROM emp
4. ) AS avg_table ON emp.sal > avg_table.avg_sal;

复制代码

如许，先算出平均薪资，再和员工表做连接，服从会比直接写 WHERE emp.sal > (SELECT AVG(sal) FROM emp) 更高。

---

外连接

外连接的核心头脑是“生存一张表的所有数据，即使另一张表没有匹配项”。这在统计报表、数据分析等场景下特别有用。
左外连接

左外连接（LEFT JOIN）的规则是：生存左表的所有记载，右表中没有匹配的部分用 NULL 补齐。
好比，我们之前提到的学生表和结果表的例子。学生表记载了所有学生的信息，结果表记载了考试结果。如果想查所有学生的结果，即使有人没到场考试，也要显示他们的名字，这时候左外连接就是最佳选择。
具体 SQL 如下：

1. SELECT student.name, score.score
2. FROM student LEFT JOIN score ON student.id = score.id;

复制代码

实行这条语句后，结果中会包罗所有学生的名字，有结果的显示具体分数，没结果的则显示 NULL。
左外连接的陷阱

虽然左外连接很实用，但新手常犯的一个错误是：在 ON 条件之外加 WHERE 条件时，不警惕过滤掉了 NULL 值。
好比，假设我们想查所有学生的结果，而且只显示结果大于 60 分的学生。如果如许写：

1. SELECT student.name, score.score
2. FROM student LEFT JOIN score ON student.id = score.id
3. WHERE score.score > 60;

复制代码

结果就会变成：只有结果大于 60 的学生会被显示，而那些没有结果的学生（score 是 NULL）会被过滤掉。这时候，左外连接的效果就失效了。
精确的做法应该是：把筛选条件放在 ON 子句里，或者在 WHERE 中答应 NULL 值存在：

1. SELECT student.name, score.score
2. FROM student LEFT JOIN score ON student.id = score.id
3. AND score.score > 60;

复制代码

如许，即使结果小于 60，学生的名字也会被生存下来，只是对应的 score 字段是 NULL。
右外连接

右外连接（RIGHT JOIN）和左外连接的逻辑是一样的，只不外方向相反。它会生存右表的所有记载，左表中没有匹配的部分用 NULL 补齐。
好比，如果我们想查所有的结果记载，即使某个结果对应的学号在学生表里找不到，也可以用右外连接：

1. SELECT student.name, score.score
2. FROM student RIGHT JOIN score ON student.id = score.id;

复制代码

这时候，结果表中的所有记载都会被生存，而学生表中找不到匹配项的部分会用 NULL 补齐。
不外，在实际开发中，右外连接的利用频率比左外连接低得多。因为大多数时候，我们更关注主表（好比学生表）的数据完整性，而结果表通常是附属表。以是，左外连接已经能满足大部分需求了。
全外连接

MySQL 自己不支持全外连接（FULL OUTER JOIN），但可以通过左外连接和右外连接的团结查询来实现。
好比，如果我们想同时生存学生表和结果表的所有记载，可以如许写：

1. SELECT student.name, score.scoreFROM student LEFT JOIN score ON student.id = score.idUNIONSELECT student.name, score.score
2. FROM student RIGHT JOIN score ON student.id = score.id;

复制代码

如许，结果中会包罗学生表和结果表中所有记载的并集，没有匹配的地方用 NULL 补齐。

---

简单案例

为了让大家更直观地理解连接的用法，咱们再来看几个实际案例。
案例 1：列出所有部分及其员工信息

需求：显示每个部分的名称，以及该部分的所有员工姓名，包括没有员工的部分。
这时候，显然要用外连接。因为内连接会过滤掉没有员工的部分，而外连接可以生存这些部分信息。
SQL 写法如下：

1. SELECT dept.dname, emp.ename
2. FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;

复制代码

实行结果中，每个部分都会被列出来，有员工的显示员工姓名，没有员工的 ename 字段是 NULL。
案例 2：查找没有订单的客户

假设我们有客户表 customers 和订单表 orders，现在想查哪些客户还没有下过订单。
这时候可以用左外连接：

1. SELECT customers.name
2. FROM customers LEFT JOIN orders ON customers.id = orders.customer_id
3. WHERE orders.order_id IS NULL;

复制代码

这里的关键是 WHERE orders.order_id IS NULL，它表示在订单表中找不到匹配记载的客户。
案例 3：合并多个表的数据

有时候，我们需要同时连接多个表。好比，查询每个员工的姓名、部分名称以及薪资品级。
假设薪资品级存储在另一张表 salgrade 中，那么 SQL 可以如许写：

1. SELECT emp.ename, dept.dname, salgrade.grade
2. FROM emp
3. INNER JOIN dept ON emp.deptno = dept.deptno
4. INNER JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

复制代码

如许，就能把三张表的数据关联起来，获取更丰富的信息。

---

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告