2. SQL 99的连接查询

SQL 99的连接查询与SQL 92的连接查询原理基本相似，不同的是SQL 99连接查询的可读性更强——查询用的多个数据表显式使用xxx join连接，而不是直接依次排列在from之后，from后只需要放一个数据表:连接条件不再放在where之后，而是提供了专门的连接条件子句。
》交叉连接(cross join):交叉连接效果就是SQL 92中的广义笛卡儿积，所以交叉连接无须任何连接条件。SQL语句如下:

select s.*, teacher_name
#SQL 99 多表连接查询的from后只有一个表名
from student_table s
#cross join交叉连接，相当于广义笛卡儿积
cross join teacher_table t;

》自然连接(natural join):自然连接表面上看起来也无须指定连接条件，但自然连接是有连接的，自然连接会以两个表中的同名列作为连接条件;如果两个表中没有同名列，则自然连接与交叉连接效果完全一样——因为没有连接条件。SQL语句如下:

select s.*, teacher_name
#SQL 99 多表连接查询的from后只有一个表
from student_table s
#natural join自然连接使用两个表中的同名列作为连接条件
natural join teacher _table t;

》using子句连接：using子句可以指定一列或多列，用于显式指定两个表中的同名列作为连接条件。假设两个表中有超过一列的同名列，如果使用natural join，则会把所有的同名列当成连接条件;使用using子句，就可显式指定使用哪此同名列作为连接条件。SQL语句如下：

elect s.*, teacher_name
#SQL 99 事表连接查询的from后只有一个表名
from student table s
# join连接另一个表
join teacher_table t
using (teacher id);

运行上面语句将出现一个错误,因为student_table表中并不存在名为tcacher_id的列。也就是说，如果使用using子句来指定连接条件，则两个表中必须有同名列，否则就会出现错误。
》on子句连接：这是最常用的连接方式，SQL 99语法的连接条件放在on子句中指定，而且每个on子句只指定一个连接条件。这意味着:如果需要进行N表连接，则需要有N-1个join…on对。SQL语句如下:

select s.*, teacher_name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
# join连接另一个表
join teacher_table t
# 使用on来指定连接条件
on s.java_teacher = t.teacher_id;

使用on子句的连接完全可以代替SQL 92中的等值连接、非等值连接，因为on子句的连接条件除等值条件之外，也可以是非等值条件。如下SQL语句就是SQL 99中的非等值连接。

select s.* ,teacher_name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
* join 连接另一个表
join teacher_tabie t
# 使用on来指定连接条件:非等值连接
on s.java_teacher > t.teacher_id;

》左、右、全外连接:这三种外连接分别使用left[outer] join、 right [outer] joinf和full [outer] join 这三种外连接的连接条件一样通过on子句来指定，既可以是等值连接条件，也可以是非等值连接条件。

下面使用右外连接，连接条件是非等值连接。

select s.*  , teacher_name
# SQL99多表连接查询的from后只有一个表名
from student_table s
# right join右外连接另一个表
right join teacher_table t
# 使用on来指定连接条件，使用非等值连接
on s.java_teacher < t.teacher_id;

下面使用左外连接，连接条件是非等值连接。

select s.*, teacher name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
# left join左外连接另一个表
left join teacher_table t
# 使用on来指定连接条件，使用非等值连接
on s.java_teacher > t.teacher_id;

运行上面两条外连接语句并查看它们的运行结果，不难发现 SQL 99 外连接与 SQL 92外连接恰好相反，SQL 99左外连接将会把左边表中所有不满足连接条件的记录全部列出，SQL 99 右外连接将会右边表中所有不满足连接条件的记录全部列出。

下面的SQL语句使用全外连接，连接条件是等值连接。

select s.*, teacher_name
# SQL 99 多表连接查询的from后只有一个表名
from student_table s
# full join全外连接另一个表
full join teacher_table t
# 使用on来指定连接条件，使用等值连接
on s.java.teacher = t.teacher_id;

SQL 99 的全外连接将会把两个表中所有不满足连接条件的记录全部列出。

注意:

运行上面查询语句时会出现错误，这是因为 MySQL 并不支持全外连接

13 子查询

子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。对于一个普通的查询语句而言，子查询可以出现在两个位置

》出现在from语句后当成数据表，这种用法也被称为行内视图，因为该子查询的实质就是一个临时视图。

》出现在where条件后作为过滤条件的值。

使用子查询时要注意如下几点。

》子查询要用括号括起来。

》把子查询当成数据表时(出现在from之后)，可以为该子查询起别名，尤其是作为前缀来限定数据列时，必须给子查询起别名。

》把子查询当成过滤条件时，将子查询放在比较运算符的右边，这样可以增强查询的可读性。

》把子查询当成过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符。 对于把子查询当成数据表是完全把子查询当做数据表来用，只是把之前的表名变成子查询(也可以为子查询起别名),其他部分与普通查询没有任何区别。下面的SQL语句示范了把子查询当成数据表的用法。

 select *
# 把子查询当成数据表
from (select * from student_table) t
where t.java_teacher > 1;

把子查询当成数据表的用法更准确地说是当成视图，可以把上面的SQL语句理解成在执行查询时创建了一个临时视图，该视图名为t，所以这种临时创建的视图也被称为行内视图。理解了这种子查询的实质后，不难知道这种子查询可以完全代替查询语句中的数据表，包括在多表连接查询中使用这种子查询。

还有一种情形:把子查询当成where条件中的值，如果子查询返回单行、单列值，则被当成一个标量值使用，也就可以使用单行记录比较运算符。例如如下SQL语句:

select *
from student_table
where java_teacher >
# 返回单行、单列的子查询可以当成标量值使用
(select teacher_id
from teacher_table
where teacher_name='Yeeku');

上面查询语句中的子查询(粗体字部分)将返回一个单行、单列值(该值就是1)，如果把上面查询语句的括号部分换成1，那么这条语句就再简单不过了一一实际上,这就是这种子查询的实质，单行、单列子查询的返回值被当成标量值处理。

如果子查询返回多个值，则需要使用in、any 和all等关键字，in可以单独使用，与前面介绍比较.运算符时所讲的in完全一样，此时可以把子查询返回的多个值当成一个值列表。SQL语句如下：

 select *
from student_table
where student_id in
(select teacher_id
from teacher_table);

上面查询语合中的子查询(粗体字部分)将返回多个值，这多个值将被当成一个值列表，只要student id与该值列表中的任意一个值相等，就可以选出这条记录。

any和all可以与>、>=、<、<=、<>、=等运算符结合使用，与any结合使用分别表示大于、大于等于、小于，小于等于、不等于、等于其中任意一个值;与all结合使用分别表示大于、大于等于、小于，小于等于、不等于、 等于全部值。从上面介绍中可以看出，=any的作用与in的作用相同。如下SQL 语句使用=any来代替上面的in.

select *
from student_table
where student_id =
any(select teacher_id
from teacher_table);

<ANY 只要小于值列表中的最大值即可,>ANY要求大于值列表中的最小值。<All要求小于值列表中的最小值，>ANY要求大于值列表中的最大值。

下面的SOL语句选出student_table表中student_id 大于teacher_table表中所有teacher id的记录.

select *
from student_table
where student id >
all(select teacher_id
from teacher_table);

还有一种子查询可以返回多行、多列，此时where子句中应该有对应的数据列，并使用圆括号将名 个数据列组合起来。SQL语句如下:

select *
from student_table
where (student_id, student_name)
= any(select teacher_id, teacher_name
from teacher_table);

14 集合运算

select语句查询的结果是一个包含多条数据的结果集,类似于数学里的集合,可以进行交(intersect)、 并(union)和差(minus)运算，select查询得到的结果集也可能需要进行这三种运算。

为了对两个结果集进行集合运算，这两个结果集必须满足如下条件。

》两个结果集所包含的数据列的数量必须相等。

》两个结果集所包含的数据列的数据类型也必须一一对应。

1. union 运算

union运算的语法格式如下:

select 语句 union select 语句

下面的SQL语句查询出所有教师的信息和主键小于4的学生信息。

# 查询结果集包含两列，第一列为int类型，第二列为varchar类型
select * from teacher_table
union
# 这个结果集的数据列必须与前一个结果集的数据列一一对应
select student_id, student_name from student_table;

2. minus 运算

minus运算的语法格式如下:

select 语句 minus select 语句

上面的语法格式十分简单，不过很遗憾，MySQL并不支持使用minus运算符，因此只能借助子查询来“曲线”实现上面的minus运算。

假如想从所有学生记录中“减去”与老师记录的ID相同、姓名相同的记录、则可进行如下的minus运算:

select student_id, student_name from student_table
minus
# 两个结果集的数据列的数量相等，数据类型一一对应，可以进行minus运算
select teacher_id, teacher_name from teacher_table;

不过，MySQL并不支持这种运算。但可以通过如下子查询来实现上面运算。

select student_id, student_name from student_table
where (student_id, student_name
not in
(select teacher_id, teacher_name from teacher_table)

3. intersect 运算

intersect 运算的语法格式如下:

select 语句 intersect select 语句

上面的语法格式十分简单，不过很遗憾，MySQL并不支持使用 intersect 运算符，因此只能借助于多表连接查询来“曲线”实现上面的 intersect 运算

假如想找出学生记录中与老师记录中的D相同、姓名相同的记录,则可进行如下的intersect运算:

select student_id, student_name from student_table
intersect
#两个结果集的数据列的数量相等，数据类型一一对应，可以进行intersect运算
select teacher_id, teacher_name from teacher_table;

不过，MySQL并不支持这种运算。但可以通过如下多表连接查询来实现上面运算

select student_id, student_name from student_table
join
teacher_table
on(student_id = teacher_id and student_name = teacher_name);

需要指出的是，如果进行intersect运算的两个select子句中都包括了where条件，那么将intersect 运算改写成多表连接查询后还需要将两个where条件进行and运算。假如有如下 intersect 运算的SQL 语句:

select student_id, student_name from student_table where student_id < 4
intersect
# 两个结果集的数据列的数量相等，数据类型一一对应，可以进行intersect运算
select teacher_id, teacher_name from teacher_table where teacher_name like '李%';

上面语句改写如下:

select student_id,  student_name from student_table
join
teacher_table
on (student_id = teacher_id and student_name = teacher_name)
where student_id < 4 and teacher_name like '李%';