主流数据库之多表连接查询中的这些连接查询你会了吗

文章目录

前言

大家好，我是ice三分颜色。个人主页：ice三分颜色的博客

本文讲了等值连接、内连接、外连接、合并结果集、不等链接。

走过路过的小伙伴们点个赞和关注再走吧，欢迎评论区交流，努力什么时候开始都不算晚，那不如就从这篇文章开始！

大家一起成长呀！笔芯

4.3、多表查询

多表查询是指在一条查询语句中，从两个或两个以上表中查询出需要的数据。

多表查询是通过各个表之间共同列的关联性(主键外键，主从表查询)来查询数据，是关系数据库查询的主要特征。

MySQL提供了以下多表间的连接查询

等值连接

等值连接是指使用相等比较符“=”指定连接条件的连接查询，这种连接查询主要用于检索主从表之间的相关数据

SELECT [表1的别名.]列名1,....[表2的别名.] 表2的列名1,...,

FROM 表1 [别名1],表2 [别名2]

WHERE 表名或别名1.column= 表名或别名2.column;

注意：1.等值连接需要两个表中的某列值完全相等时才进行连接。

2.要连接的表都要先写在FROM后，表名用逗号分开。

3.连接的条件放在WHERE子句中。WHERE后用表名还是别名，要看FROM后设没设别名。要进行n个表的连接至少需要n-1个连接条件。

4.如果查询的列在参加连接的各表中是唯一的，则可以省略表名前缀。如果多个表中有相同列名时，在这些列的前面要加上表名来区别他们

例:查询每位员工所属部门的名称和地点（因为员工表中的deptno列是外键，其取值来源于部门表的deptno列，因此通过deptno列可以实现员工表和部门表的等值连接）

SELECT emp.empno,emp.ename,emp.deptno,dept.dname,dept.loc

FROM employee emp,department dept

WHERE emp.deptno=dept.deptno;

-- 将两个表及其对应列用相等比较符“=”等值连接起来查询。

例：查询工资为2000元或以上的员工所属部门和所在的具体地点。

SELECT e.empno,e.ename,e.deptno,d.dname,d.loc

FROM employee e,department d

WHERE e.deptno=d.deptno AND e.sal>=2000

ORDER BY e.empno ASC;

内连接

内连接与等值连接功能相同，都用于返回满足连接条件的记录。其中:INNER JOIN表示内连接，ON用以指定两个表间的连接条件。

SELECT [表1的别名.]列名1,....[表2的别名.] 表2的列名1,...,

FROM 表1 [别名1] [INNER] JOIN 表2 [别名2]

ON 表1.列名=table2.列名;

例：使用内连接查询职位为文员和分析员的员工的姓名、职位、直属经理姓名和部门名称，显示结果按员工编号升序排序。

SELECT e.ename '员工名称', e.job '职位' , m.ename '经理名称', d.dname '部门名称'FROM employee e

JOIN employee m ON e.mgr=m.empno

JOIN department d ON e.deptno=d.deptno

WHERE e.job IN('CLERK','ANALYST')

ORDER BY e.empno;

外连接

外连接是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足条件的记录。外连接具体又可分为3种:左外连接、右外连接、交叉连接

左外连接和右外连接

不仅会返回连接表中满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符左边表的其他行

SELECT [表1的别名.]列名1,....[表2的别名.] 表2的列名1,..., FROM 表1 [别名1]

LEFT [OUTER] JOIN 表2 ON 表1.列名=table2.列名;

与左外连接功能类似，不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符右边表的其他行。

SELECT [表1的别名.]列名1,....[表2的别名.] 表2的列名1,..., FROM 表1 [别名1]

RIGHT [OUTER] JOIN 表2 ON 表1.列名=table2.列名;

例：查看部门表会发现其中有一个第40号部门，而员工表中并没有一名员工是这个部门的，因此使用等值连接关联查询员工及所属部门信息时，并不显示此部门的信息。此时若需要同时将所有部门(包括没有员工的部门)的信息都显示出来。

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM department d

LEFT JOIN employee e ON d.deptno=e.deptno

ORDER BY d.deptno DESC;

例：查询所有部门信息（包括没有员工的)及其对应的员工信息。

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM employee e

RIGHT JOIN department d ON e.deptno=d.deptno

ORDER BY d.deptno DESC;

交叉连接

是在没有where子句的情况下，产生的表的笛卡儿积。两个表作交叉连接时，结果集大小为二者行数之积。该种方式在实际过程中用的很少。

例:显示employee表和department表的笛卡儿积，共10*4=40条记录。

SELECT e.ename,e.deptno,d.deptno,d.dname,d.loc FROM employee e

CROSS JOIN department d;

注意: 1.从理论上说，对于使用select语句进行连接的表数目没有上限。但在一条select语句中连接的表多于10个，那么数据库就很可能达不到最优化设计，MySQL引擎的执行计划会变得非常繁琐。

2.对于3个以上关系表的连接查询，一般遵循下列规则:连接n个表至少需要n-1个连接条件，以避免笛卡儿积的出现。为了缩小结果集，采用多于n-1个连接条件或使用其他条件都是允许的。

合并结果集

union操作符可以将多个select语句的返回结果组合到一个结果集中。

当要检索的数据在不同的结果集中，并且不能够利用一个单独的查询语句得到时，可以使用union合并多个结果集。

将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。

使用union合并两个查询结果集时，所有查询中的列数和列的顺序必须相同且数据类型必须兼容。

格式

select_statement1

UNION[ALL]

select_statement2

其中:select_statement: select语句。UNION:指定组合多个结果集并返回为单个结果集。

All:将所有行合并到结果中，包括重复的行。如果不指定，将自动删除重复的行。

例:表的部分查询结果集合并（先创建一个emp表，再查询）

CREATE TABLE emp AS SELECT empno,ename,job FROM employee;

SELECT empno,ename,job FROM emp WHERE ename like "%AR%"

UNION

SELECT empno,ename,job FROM employee WHERE ename like "%AM%";

不等连接

不等连接是指两个表之间通过相关的两列的比较操作而进行的相互关联。比较操作一般采用“>”、“<”、“BETWEEN...AND”等操作符。

例：查询员工的编号、姓名、职位、工资、以及工资所对应的级别。其中工资级别保存在SALGRADE (工资等级表)中。

先查询下两张表，如下所示（employee表和salgrade表，没有的自己创建哦）

SELECT*from employee;

SELECT*from salgrade;

SELECT e.empno,e.ename,e.job,e.sal,s.grade

FROM employee e,salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal;