5.2　联合查询

5.2.1　内连接查询

在MySQL中，可以通过两种语法形式来实现连接查询：一种是在FROM子句中利用逗号区分多个表，在WHERE子句中通过逻辑表达式来实现匹配条件，从而实现表的连接，这是早期MySQL连接的语法形式；另一种是ANSI连接语法形式，在FROM子句中使用“JOIN…ON”关键字，而连接条件写在关键字ON子句中。推荐使用ANSI语法形式的连接。

在MySQL中内连接数据查询通过“INNER JOIN…ON”语句来实现，语法形式如下所示。

    SELECT field1,field2,…,fieldn FROM tablename1
    INNER JOIN tablename2 [INNER JOIN tablenamen] ON CONDITION

其中，参数fieldn表示要查询的字段名，来源于所连接的表tablename1和tablename2，关键字INNER JOIN表示表进行内连接，参数CONDITION表示进行匹配的条件。

当表名特别长时，直接使用表名很不方便，或者在实现自连接操作时，直接使用表名无法区别表。为了解决这一类问题，MySQL提供了一种机制来为表取别名，具体语法如下：

其中，参数tablenamen为表原来的名字，参数othertablenamen为新表名，之所以要为表设置新的名字，是为了让SQL语句代码更加直观、更加人性化和实现更加复杂的功能。

按照匹配情况，内连接查询可以分为如下三类：

• 自连接
• 等值连接
• 不等连接

1. 自连接

内连接查询中存在一种特殊的等值连接——自连接。所谓自连接，就是指表与其自身进行连接。

【示例5-6】分别使用WHERE和自连接方式查询学生“Alicia Florric”所在班级的其他学生，操作如下。

（1）查询学生“Alicia Florric”所在班级的其他学生，SQL语句如下：

    SELECT ts1.stuid,ts1.name,ts1.classno
    FROM t_student AS ts1,t_student AS ts2
    WHERE ts1.classno=ts2.classno AND ts2.name='Alicia Florric';

（2）上述SQL语句采用关键字WHERE设置匹配条件。我们也可以用ANSI连接语法形式，具体SQL语句如下：

    SELECT ts1.stuid,ts1.name,ts1.classno
    FROM t_student ts1 INNER JOIN t_student ts2
    ON ts1.classno=ts2.classno AND ts2.name='Alicia Florric';

2. 等值连接

内连接查询中的等值连接就是在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件。

【示例5-7】分别使用WHERE和等值连接方式查询学生以及班级信息，操作如下：

（1）查询每个学生的编号、姓名、性别、年龄、班级号、班级名称、班级位置和班主任信息，具体SQL语句如下：

    SELECT s.stuid,s.name,s.gender,s.age,s.classno,c.cname,c.loc,c.advisor
    FROM t_student s,t_class c WHERE s.classno=c.classno;

（2）上述SQL语句使用的是关键字“SELECT FROM WHERE”，也可以采用ANSI连接语法形式，具体SQL语句如下：

    SELECT s.stuid,s.name,s.gender,s.age,s.classno, c.cname,c.loc,c.advisor
    FROM t_student s INNER JOIN t_class c
    ON s.classno=c.classno;

3. 不等连接

内连接查询中的不等连接就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，还可以使用关系运算符，包含“>”“>=”“<”“<=”和“!=”等运算符号。

【示例5-8】分别使用WHERE和不等连接方式查询和学生“Alicia Florric”不在同一班级的其他学生信息，操作如下：

（1）查询和学生“Alicia Florric”不在同一个班级且年龄大于“Alicia Florric”的学生的编号、姓名、性别、年龄、班级号、班级名称、班级位置和班主任信息、成绩总分，具体SQL语句如下：

（2）上述SQL语句用的是关键字“SELECT FROM WHERE”，也可以采用ANSI连接语法形式，具体SQL语句如下：

5.2.2　外连接查询

在MySQL中，外连接查询会返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN…ON”来实现。外连接数据查询语法形式如下：

    SELECT field1,field2,…,fieldn
    FROM tablename1 LEFT|RIGHT|FULL [OUTER] JOIN tablename2
    ON CONDITION

在上述语句中，参数fieldn表示所要查询的字段名字，来源于所连接的表tablename1和tablename2，关键字OUTER JOIN表示表进行外连接，参数CONDITION表示进行匹配的条件。

按照外连接关键字，外连接查询可以分为以下三类：

• 左外连接
• 右外连接
• 全外连接

1. 左外连接

外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。

【示例5-9】分别使用左外连接和自连接方式查询学生信息及班级信息，操作如下：

（1）查询所有学生的学号、姓名、班级编号、班级名、班级地址和班主任信息，具体SQL语句如下：

    SELECT s.name,c.cname,c.loc,c.advisor
    FROM t_student s LEFT OUTER JOIN t_class c
    ON s.classno=c.classno;

（2）修改上述SQL语句为等值连接的内连接，SQL语句如下：

    SELECT s.name,c.cname,c.loc,c.advisor
    FROM t_student s INNER JOIN t_class c
    ON s.classno=c.classno;

2. 右外连接

外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。

【示例5-10】查询所有班级的所有学生信息。具体SQL语句如下：

    SELECT s.stuid,s.name,c.classno,c.cname,c.loc,c.advisor
    FROM t_student s RIGHT OUTER JOIN t_class c
    ON s.classno=c.classno;

3. 全外连接

全外连接实际上是左外连接与右外连接去重后的合集。

5.2.3　合并查询数据记录

在MySQL中通过关键字UNION来实现并操作，即可以通过其将多个SELECT语句的查询结果合并在一起，组成新的关系。在MySQL软件中，合并查询数据记录可通过SQL语句UNION来实现，具体语法形式如下：

    SELECT field1,field2,…,fieldn
    FROM tablename1
    UNION | UNION ALL
    SELECT field1,field2,…,fieldn
    FROM tablename2
    UNION | UNION ALL
    SELECT field1,field2,…,fieldn
    FROM tablename3
    ……

上述语句中存在多个查询数据记录语句，每个查询数据记录语句之间使用关键字UNION或UNION ALL进行连接。

1. 带有关键字UNION的并操作

关键字UNION会把查询结果集直接合并在一起。使用UNION合并查询数据记录的SQL语句示例如下：

    SELECT * FROM t_developer UNION SELECT * FROM t_tester;

2. 带有关键字UNION ALL的并操作

关键字UNION ALL会把查询结果集直接合并在一起，SQL语句示例如下：

    SELECT * FROM t_developer UNION ALL SELECT * FROM t_tester;

5.2.4　子查询

所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。

通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。

1. 带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。

【示例5-11】查询薪资水平为高级的所有员工的编号、姓名、性别、年龄和工资。SQL语句如下：

    SELECT * FROM t_employee
    WHERE salary>=(SELECT salary FROM t_slevel WHERE level=3)
    AND salary<(SELECT salary FROM t_slevel WHERE level=4);

该语句在子查询中使用了=、>=和<三种运算符。

2. 带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，SQL示例如下：

NOT IN的用法与IN相同。

3. 带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

【示例5-12】查询数据库company的表t_dept中是否存在deptno为4的部门，如果存在，再查询表t_employee的记录。SQL示例语句如下：

    SELECT * FROM t_employee
    WHERE EXISTS (SELECT deptname FROM t_dept WHERE deptno=4);

4. 带关键字ANY的子查询

关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。例如，需要查询哪些学生可以获取奖学金，那么首先要有一张奖学金表，从表中查询出各种奖学金要求的最低分，只要一个同学的乘积大于等于不同奖学金最低分的任何一个，这个同学就可以获得奖学金。关键字ANY通常和比较运算符一起使用。例如，“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。

【示例5-13】查询数据库school的表t_student中哪些学生可以获得奖学金。学生的成绩达到其中任何一项奖学金规定的分数即可，SQL语句示例如下：

5. 带关键字ALL的子查询

关键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。例如，需要查询哪些同学能够获得一等奖学金，首先要从奖学金表中查询出各种奖学金要求的最低分。因为一等奖学金要求的分数最高，只有当成绩高于所有奖学金最低分时，这个同学才可能获得一等奖学金。关键字ALL也经常与比较运算符一起使用。例如，“>ALL”表示大于所有值，“<ALL”表示小于所有值。

【示例5-14】查询数据库school的表t_student中哪些学生可以获得一等奖学金，即学生的总成绩要达到一等奖学金规定的分数，而一等奖学金是最高奖学金。SQL语句示例如下：