目录
4.2 查询和SMITH的部门和岗位完全相同的所有雇员，不包含SMITH本人
5.1 显示每个高于自己所处部门平均工资的部门员工的姓名、部门、工资与平均工资
5.2 查找每个部门工资最高的人的姓名、工资、部门、最高工资
一、多表查询的概念
在以前的文章中写的基本都是单表查询，即只对一张表查询。但是在实际中如果只能对一张表进行查询是远远不够的，因为实际开发中的数据往往来自不同的表，所以需要多表查询。
在了解多表查询前，我们要先准备好如下几个表：
其实就是上一篇文章“表的增删查改”中用的同一组表。如果大家没有，可以直接到网上搜“oracle 9i经典测试表”，可以直接找文章下载使用。表的内容也就不再过多介绍了。后面的几乎所有案例，都会用这几张表做例子。
在这后面的所有内容，都是属于多表查询。
二、笛卡尔积
1. 笛卡尔积的概念
在以前的案例中，我们查询表中数据的时候，from后面都是只带了一张表的。
但实际上，from后面其实是可以用逗号分隔，带上多张表的。这些表，既可以是不同的表，也可以是相同的表。
如果大家仔细观察一下这些组合就会发现，在多个表组合形成的新表中，其实就是将不同的表中的数据进行穷举组合，然后形成一张新表。
这种将不同表的数据进行穷举组合的方法，就叫做“笛卡尔积”。在以前的文章中讲过“mysql中一切皆表”。因此，笛卡尔积之后形成的结果，我们其实也可以看成一张表。对笛卡尔积后的结果进行操作，其实也就是对另一张单独的表进行操作。
那这个笛卡尔积到底有什么用呢？在这里就举几个例子为大家展示。
2. 笛卡尔积使用案例
2.1 显示雇员名、雇员工资以及所在部门的名字
在这道题中，要求显示雇员名和雇员工资，这个好办，因为它们都在emp表内。但是所在部门的名字就不一样了，emp表内只有所在部门的编号，名字则在另一张dept表内。这就要求我们要从两张表中查询数据。
从上面的笛卡尔积中我们可知，from后面是可以跟多张表的，因此，我们先将这两张表的数据组合穷举出来：
注意，由于这两张表组合后太大了，所以这里并没有将组合全部截完，而只截了一部分。
当我们拿到了这两张表组合后的所有数据后，就可以开始筛选了。要知道，我们仅仅是需要知道雇员的信息，但是当这两张表组合后我们会发现，由于emp表中的部门编号来源于dept表，因此，一个雇员的部门编号在emp表和dept表内应该是一样的。所以，添加筛选条件：
注意，当使用多表查询的时候，如果在指令中需要使用表的列名，就需要按照“表名.列名”的方式使用，以用于区分不同表的列数据。使用方法就和C/C++中使用对象内的数据差不多。
当我们拿到上面的数据后，就可以保证上面的数据中每个雇员的信息都是一一对应的。因此，我们只需要在select后面加上要显示的数据即可：
2.2 显示部门号为10的部门名，员工名和工资
这道题其实和上面的题差不多，都是要从emp和dept两张表中获取数据，不同的是这次不再是获取全部数据，而是获取部门号为10的员工的数据。
对于这道题，我们采用逐步分解的方法。首先，由于它是要到两张表中找数据，所以我们首先将两张表组合起来：
然后，要找部门号为10的员工的信息，所以直接将这两个表中部门号相同且为10的员工筛选出来：
最后，由于是只显示部门号为10的部门名、员工名和工资，所以在select后面添上要显示的内容：
2.3 显示所有员工的姓名、工资和工资级别
这道题需要的数据也是存在两张表中的，分别是emp表和salgrade表内。salgrade表内的数据如下所示：
对于这道题，由于需要用到两张表的数据，同样的，先组合起来：
此时获得的就是每个员工对应的所有信息了。在这道题中要求显示工资级别， 因此使用条件运算符between and筛选出每个员工的工资级别和符合要求的数据。
最后，由于只要求显示姓名、工资和工资级别，所以添加显示内容：
3. 自连接
3.1 自连接的概念
自连接，其实也是基于笛卡尔积的。在上文中我们就实验过，在from后面是可以用不同的表组合的。但是，其实同一张表也是可以组合的。
如果用相同的表进行组合，就需要给这个表分别取不同的别名。这其实就是自连接。
因此，自连接其实就是指在同一张表连接查询。
那么，我们在什么时候会用到自连接呢？为了让大家更清楚的看到实际情况，我们就用一个案例来说明。
3.2 自连接案例：显示员工FORD的上级领导的编号和姓名
关于员工的所有信息，我们其实都存在了emp表内。在这张表中，empno是员工编号，mgr是员工的上级领导的编号。该公司的所有员工都在这张表中。
在这里介绍两个解决方法。
方法一：拆分题目，用两条sql语句完成
首先，由于是要找FORD员工的上级领导，因此，我们可以将FORD找出来：
找到FORD员工后，就可以根据它的mgr找到它的上级领导：
虽然这种方法可以完成题目要求，但是这种方法使用两条sql语句，那如果我们想只用一条sql语句完成呢？
方法二：使用自连接完成
由于这个题目的要求是拿着表中的一个数据再到表中找另一个数据，因此我们可以先将表组合，形成一张新的表：
由于我们是需要找FORD员工的信息，所以将FORD员工的信息筛选出来：
此时我们就获得了这个员工在表中的所有组合。由于是要找它的领导，因此，该领导的编号应该和该员工的领导编号相同，再次筛选：
此时就得到该领导和该员工的组合数据了。因此根据题目要求，显示领导编号和领导姓名即可：
此时，就成功获取到了FORD员工的领导编号和领导姓名。
三、子查询
1. 子查询的概念
在上文中我们说过，from后面是可以跟多张表，进行多表查询。但我们我们可能与遇到一些特殊情况，需要对一张表或多张表进行多次select查询。此时就可以采用子查询。
子查询，简单来讲就是将select语句嵌入到其他sql语句中，也叫做嵌套查询。在子查询中，就是依赖于子查询查询出来的结果在一个sql语句中进行二次或多次查询。
根据查询出来的结果，可以将子查询分为单行子查询、多行子查询和多列子查询。
2. 单行子查询
2.1 单行子查询的概念
单行子查询，含义就是子查询只返回一条结果，即返回单列单行数据的子查询。
为了方便大家理解，在这里就举几个使用示例。
2.2 显示和SMITH同一部门的员工
要显示和SMITH同一部分的员工，那我们就首先要拿到SMITH的部门编号。
当我们就拿到了SMITH的部门编号后，就可以拿着这个编号去查询所有部门编号为20的员工了。此时大家可能有两个思路，一个是分为两个sql语句；另一个就是用笛卡尔积进行筛选。但在这里，我们还可以采用子查询的方式。
要查询部门编号为20的员工，就需要使用where子句。如果我们用两条sql语句，第二条sql语句就是用第一条sql语句查出来的结果20进行二次查询：
既然如此，那我们不妨将第一条sql语句放入另一条sql语句中的where子句的判断条件中：
此时我们就拿到了需要的结果了。这就是子查询的使用。注意，在使用子查询时，需要将子select语句用括号括起来，表示将其看做一个整体使用。
可以发现，在上面的这个例子中，子查询只返回了单列单行的deptno=20的数据。这就是单行子查询。
3. 多行子查询
3.1 单行子查询概念
多行子查询，就是指只返回一列，但是有多行数据的子查询。
为了更好的使用这些子查询，就需要使用到一些特殊的关键字。
3.2 in关键字
为了更好的解释in关键字的作用，用一个案例来说明。
案例：查询10号部门的工作岗位相同的雇员的名字、岗位、工资、部门号，但是不包含10号部门自己。
要解决这道题，同样可以用子查询。首先，既然要查询和10号部门的工作岗位相同的雇员信息，那我们首先可以拿到10部门的所有工作岗位。
为了防止出现有员工的工作岗位相同的情况，最好对结果用distinct去重。
有了工作岗位后，就可以将这个结果当做子查询结果，再次查询：
可以看到，此时就出现了报错，告诉我们子查询返回了太多行了。因此，在有多行数据返回情况下，就不能直接将其作为判断条件，而需要使用“in”。
in的作用，大家就可以看成是将单行子查询的结果看做一个集合，只要需要对比的列数据在子查询结果中出现，就为真。即只要在这个集合中出现的数据，外部的子查询都可以认为是真，需要将匹配数据查找出来。
在题目中要求返回雇员的名字、岗位、工资、部门号，但不包括10号部门的。因此，再加上筛选条件和显示内容即可：
在上面sql语句的判断条件中，“<>”就是不等于的意思，和"!="是一样的，在之前的文章中已经介绍过了。
3.3 笛卡尔积与子查询的组合
在上面的内容中，我们通过多行子查询得到了一张新的表，那如果现在我们在上面的题目的基础上，再加一个显示雇员的部门名字的要求呢？
部门名字的数据在dept表中，而上面的多行子查询得到的结果都是在一张表内。但是在上文中讲过，“mysql下一切皆表”。这就意味着，上面的多行子查询出来的结果，其本质依然是一张表。既然是一张表，那就应该可以和其他表做笛卡尔积。测试一下：
我们成功的将empt与子查询的结果做了一次笛卡尔积。注意，对于这种经过查询后得出来的逻辑上的表，在做笛卡尔积时需要用括号括起来将其当做一个整体，并取一个别名，告诉mysql'该表的名字。
既然我们得到了这么一张新表，那我们只需要筛选出里面的正确信息即可。首先因为查询结果中得到的部门号来自于dept表，所以筛选出部门号相同的雇员。然后再显示题目要求的信息即可：
大家在实际写的时候不用换行也可以，这里换行与不换行没有影响，单纯是因为换行比较容易看清这条sql语句的结构而已。
3.4 all关键字
同样的，为了方便大家理解all关键字，也用一个案例来说明。
案例：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。
对于这道题，一种解法就是子查询+聚合函数。这里要求比部门30的所有员工的工资高，那我们只需要用聚合函数找出最大值，然后再用一条select语句查询比这个最大值还大的员工的工资即可：
那如果现在我们不想用聚合函数+单行子查询，而是想用多行子查询呢？
首先，找到表中30号部门的所有人的工资：
然后，我们再用一条select语句进行比对。在这里，就不能使用in关键字了，因为in关键字是比对列数据是否存在于子查询结果，只要有一个存在即为真。但这里是需要对所有结果比对，当列数据大于大于所有的子查询结果才为真。
因此，all关键字的作用就是与子查询的所有结果进行比对，当比对的所有结果同时为真时，才为真。
3.5 any关键字
同样的，举一个案例为大家说明any关键字的作用。
案例：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包括自己部门的员工）
对于这道题，我们同样可以用聚合函数+单行子查询解决。因为要比部门30的任意员工高，就意味着只需要比部门30的最低工资高即可。
因为可以包含自己部门的员工，就意味着我们甚至不需要筛除同部门员工，很简单。
那如果要用多行子查询呢？
首先，查询部门30的所有员工的工资：
然后，再用一条select语句对该子查询语句的结果进行比对查询。但此时就不能用in和all关键字了，因为in是对比是否存在，all是对子查询的所有结果进行比对，同时满足条件才为真。
此时，就可以使用any关键字：
由此，any关键字的作用就是，与子查询结果的所有结果进行对比，只要有一个满足条件，就为真。
3.6 关键字总结
总结起来，这三个关键字的作用分别为：
（1）in关键字：判断筛选条件是否存在于子查询结果中，存在即为真。
（2）all关键字：判断筛选条件与子查询中的所有结果比对后是否为真，全部结果为真即为真。
（3）any关键字：判断筛选条件与子查询中的任意结果比对后是否为真，只要有一个为真即为真。
4. 多列子查询
4.1 多行子查询的概念
通过上文大家应该知道了，单行子查询就是返回单行单列的数据；多行子查询就是返回多行单列的数据。这两个子查询都是针对于单列情况的。那如果出现查询结果是多列呢？此时就需要使用多列子查询了。
多行子查询，就是指返回多列数据的子查询语句。
为了方便让大家看到实际使用，用一个案例给大家说明。
4.2 查询和SMITH的部门和岗位完全相同的所有雇员，不包含SMITH本人
在这道题中，要求查询和SMITH的部门和岗位完全相同的所有雇员。因此，我们可以先找到SMITH的部门和岗位：
很明显，这里就是两列信息了。此时大家可能就会想用笛卡尔积的方式解决这道题。确实，用笛卡尔积确实可以解决这道题。我们只需要将emp表与这张新形成的表组合起来，然后筛选出其中部门和岗位与SMITH相同的雇员，最后再剔除SMITH即可。
那如果不用笛卡尔积的方式，而是用多列子查询，我们应该如何解决呢？
很简单，在单行和多行子查询中，在where后面的判断我们都只带了一个列数据与子查询结果对比。既然需要对比多个列数据，那我们在与子查询结果比对时，带上多个列数据不就可以了吗？
可以看到，此时就能拿到要求的数据了。注意，在使用多个列数据与子查询结果对比时，需要用括号将列数据括起来。
最后，我们剔除掉SMITH即可：
使用起来也是比较简单的。当然，这里的多列子查询中是多列单行。如果遇到多列多行，只需要在where的判断条件中带上多列子查询的对应关键字即可。
从上面的子查询中我们就可以得出一个结论——在任何时刻查询出来的临时表结构，它的本质也是一张逻辑上的表结构。既然是表结构，我们就可以将其当做一张普通的表进行处理。
因此，在处理mysql中的表时，将所有得到的逻辑表结构都视为一张普通的表的话，处理起来就非常的轻松，就是不断形成一张张普通表进行增删查改。
5. 在from子句中使用子查询
在上面的所有子查询中，都是将子查询放在where子句中充当判断条件。但是，子查询语句也是可以出现在from子句后面的。其原因就是子查询结果形成的临时表也是表结构，而from的作用就是告诉mysql数据库要去哪个表中找数据。因此也可以放在from后面与其他表形成笛卡尔积。
这种使用方法，在上面一些案例中已经使用过了，但并没有仔细讲。
同样的，为了方便大家理解，这里举一个案例进行说明。
5.1 显示每个高于自己所处部门平均工资的部门员工的姓名、部门、工资与平均工资
看到这道题中的自己部门平均工资，大家可能会想到用group by对每个部门分组，然后用聚合函数计算出每个部门的平均工资。通过这种方式，就能够获得每个部门的平均工资了。
但是，这种方法是不可行的。因为当我们获得了每个部门的平均工资后，就需要使用多列子查询。但是，在多列子查询中我们就无法处理了。因为我们知道，where子句是会比select子句先执行的，这就意味着聚合函数得到的平均工资的别名，无法在where中使用。同时，如果我们将聚合函数的值放到where，也会出现报错。由此， 如果采用多列子查询，就很难解决数据比对的问题。
由此，我们就可以采用在from子句中使用子查询的方法。将子查询得到的部门平均工资当做一张表，与emp表做笛卡尔积形成组合。
注意，如果不是数据库中真实存在的表，而是逻辑上的表，就需要带上别名。
有了这张新表后，再来看题目要求。题目中要求是高于自己所处部门的平均工资，因此，这两张表中的部门号应该相同：
最后，要求显示高于自己所处部门的平均工资，因此用emp表的工资与tmp表的平均工资做对比，然后显示要求的数据：
那如果我们再修改一下这道题，要求显示部门名呢？很简单，上面的查询结果不是已经形成了一张表了么。我们拿着这张表再去与保存了部门名的dept表做笛卡尔积，然后筛选出里面部门编号相同的有效数据，再显示出对应数据即可：
看起来很复杂， 其实就是将一张张表组合起来筛选出特定列。
5.2 查找每个部门工资最高的人的姓名、工资、部门、最高工资
这道题，我们同样是看成一张张表来解决。首先，要查找每个部门工资最高的人的信息，因此，先用聚合函数加分组找到他的部门号和工资。
得到这几个部门的部门号和最高工资后，再将这个查询结果看成一张单独的表，与emp表做笛卡尔积：
此时就得到了所有的穷举组合。再比对部门号，找到部门号相同的有效数据：
此时我们就拿到了每个部门的最高工资的人的数据。但是，这张表在经过笛卡尔积后，会有重复的部分。因此，去重加、筛选出工资与最高工资相同的人，然后显示特定数据：
5.3 显示每个部门的信息（部门名，编号，地址）和人员数量
对于这道题，要显示每个部门的信息和人员数量。但是emp表中是没有每个部门的人员数量的。因此，先用聚合函数+分组形成新表：
表有了，但是部门的信息此时不在emp表中，而是在dept表中。因此，将新表与dept表做笛卡尔积：
拿到所有的组合后，筛选出两张表中部门名相同的有效人员：
此时就将题目要求的所有信息拿到了。
总而言之，在mysql的查询中，只需要记住“mysql一切皆表”这句话即可。将一个个复杂的问题划分为对一张张表进行增删查改，就是非常简单的了。而所谓的mysql的“多表查询”，其实并不存在什么多表，永远都是对一张新表做查询。
因此，解决多表问题的本质，就是想办法将多张表转化为一张表。
四、合并查询
在实际中，为了合并多个select的执行结果，可以使用集合操作符union和union all。
union操作符用于取得两个结果集的并集。当使用该操作符后，会自动去掉结果集中的重复行。
union all操作符也是用于取得两个结果集的并集，当使用该操作符后，不会去掉结果集的重复行。
因此，union和union all的区别就是是否会去掉重复行。
1. 案例：查询工资大于2500或职位是MANAGER的人
对于这道题，我们可以将其分为两张表，分别是工资大于2500的表和职位是MANAGER的表。将这两张表形成交集，然后去重就可以得到结果。而union可以自动去重，所以去重工作无需我们自己做：
而如果用union all，就不会去重：
但是要注意，要形成交集，就势必决定了表的列数和表的列名必须是一样的，否则就无法形成并集。
同时这个题目，我们直接在emp表内添加两个判断条件解决：
因此，实际上来说，合并查询基本没什么太大作用，可以被其他查询替代。在实际中几乎很少会用到合并查询。
五、表的内外连接
表的连接分为内连接与外连接，在上面的所有连接方式，其实都是内连接。
1. 内连接
1.1 内连接的含义
内连接实际上就是利用where子句对两张表形成的笛卡尔积进行筛选，前面学习的查询都是以内连接方式查询的，内连接也是在开发过程中使用的最多的连接查询。
内连接的语法也很简单，就是将以前进行笛卡尔积中的连接两个表的逗号替换为inner join，然后把where替换为on。如下图：
虽然我们以前写的都是内连接，但内连接的标准语法如上所示。
1.2 案例：显示SMITH的名字和部门名称
为了让大家看到内连接的实际使用，就举一个例子说明。
这道题很简单，因为部门名称在dept表，而SMITH的名字在emp表，所以需要用笛卡尔积形成新表。在形成新表后，再判断表中数据的部门编号是否相同以获得有效数据。最后在显示内容中填上名字和部门名称即可：
上文中说了，这其实也是内连接。但如果要用内连接的标准语法，就需要将表之间的逗号替换为inner join，where替换为on：
此时就获得了要求的数据。如果还有其他数据，可以用or、and等逻辑运算符连接。当然，也可以继续使用where：
这两种写法是等价的。至于大家想用那种，就因人而异。
2. 外连接
外连接分为左外连接和右外连接。这里分别介绍一下。外连接，其实就是“完全显示表”。
2.1 左外连接
在多表查询中，左侧的表完全显示的话，我们就认为是“左外连接”。这个完全显示指左侧的表不要经过任何筛选，就保持全貌，如果表与表之间的行数据配不上，就算另一张表为空，左侧的表也必须按原貌显示。
语法如下：
其实就是将内连接的inner替换为了left。
为了方便看到现象，我们先创建如下两张表：
然后向这两张表中分别插入如下内容：
在这张表，我们特意将其设置为了两张表的数据无法完全匹配的情况：
由此，我们通过一个案例来测试：
案例：查询所有学生的成绩，如果这个学生没有成绩，也要将这个学生的个人信息显示出来。
在这个题目中，要求完整显示学生个人信息，所以左侧的表就是stu。写出如下sql语句：
如果将左外连接换成内连接，就只会显示id出现在exam中的数据：
2.2 右外连接
当理解了左外连接后，大家就应该理解右外连接了。
右外连接，其实就是在联合查询时，将右侧的表完全显示。
其实右外连接没什么太大意义，毕竟sql语句中表的位置是可以换的，所以遇到右外连接的情况，调换一下表顺序，使用左外连接即可。不过这里还是简单介绍一下。
右外连接的语法如下：
其实就是将左外连接的left替换为了right。
用和左外连接同样的两个表来举个例子。
案例1：查询所有学生的成绩，即时这个成绩没有学生与它对应，也要完全显示。
这道题写起来就非常简单了：
为了方便大家理解，再用上文中使用emp表和dept表再举一个例子。
案例2：列出部门名称和这些部门的员工信息，同时列出没有员工的部门。
首先，部门名称在dept表，员工信息在emp表，所以将这两张表做笛卡尔积，然后筛选出其中部门编号相同的有效数据。
此时就拿到了所有有部门的员工信息了。但是这里要求列出没有员工的部门，即dept表必须完整显示。因此，将逗号替换为right join，where替换为on：
此时就可以看到结果了，部门编号为40的部门中没有员工。当然，也可以用左连接，颠倒一下表的顺序即可。
434
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
