多表连接查询

多表连接查询有两种规范，较早的SQL 92规范支持如下几种多表连接查询。

  • 等值连接
  • 非等值连接
  • 外连接
  • 广义笛卡尔积

SQL 99范围提供了可读性更好的多表连接语法，并提供了更多类型的连接查询。SQL 99支持如下几种多表连接查询

  • 交叉连接
  • 自然连接
  • 使用using子句的连接
  • 使用on子句的连接
  • 全外连接或者左、右连接
SQL 92的连接查询

SQL 92的多表连接语法比较简洁，这种语法把多个数据表都放在from之后，多个表之间以逗号隔开；连接条件放在where之后，与查询条件之间用and逻辑运算符连接。如果连接条件要求两列值相等，则称为等值连接，否则称为非等值连接；如果没有任何连接条件，则称为广义笛卡尔积。SQL 92中多表连接查询的语法格式如下：

select column1,column2 ...
from table1, table2 ...
[where join_condition]

如下SQL语句查询出所有学生的资料以及对应的老师姓名

select s.*, teacher_name 
# 指定多个数据表，并指定表别名
from student_table s, teacher_table t
# 使用where指定连接条件
where
s.java_teacher=t.teacher_id;

实际上，多表查询的过程可理解为一个嵌套循环，这个嵌套循环的伪码如下

// 依次遍历teacher_table 表中的每条记录
for t in teacher_table
{
	// 遍历student_table表中的每条记录
	for s in student_table
	{
		// 当满足连接条件时，输出两个表连接后的结果
		if(s.java_teacher=t.teacher_id)
			output s + t
	}
}

理解了上面的伪码后，我们就可以很轻易地理解多表连接查询的运行机制。如果求广义笛卡尔积，则where子句后没有任何连接条件，相当于没有上面的if语句，广义笛卡尔积的结果会有n x m 条记录。只要把where后的连接条件去掉，就可以得到广义笛卡尔积

# 不使用连接条件，得到广义笛卡尔积
select s.*, teacher_name
# 指定多个数据表，并指定表别名
from student_table s , teacher_table t;

如果还需要对记录进行过滤，则将过滤条件和连接条件使用and连接起来

select s.*, teacher_name
# 指定多个数据表，并指定表别名
from student_table s, teacher_table t
# 使用where指定连接条件，并指定student_name列不能为null
where s.java_teacher=t.teacher_id and s.student_name is not null;

自连接只是连接的一种用法，并不是一种连接类型，不管是SQL 92还是SQL 99都可以使用自连接查询。自连接的本质就是把一个表当成两个表来用

SQL 99的连接查询

SQL 99的连接查询与SQL 92的连接查询原理基本相似，不同的是SQL 99连接查询的可读性更强，查询用的多个数据表显式使用xxx join 连接，而不是直接依次排列在from之后，from后只需要放一个数据表；连接条件不再放在where之后，而是提供了专门的连接条件子句。

交叉连接（cross join）

交叉连接效果就是SQL 92中的广义笛卡尔积，所以交叉连接无须任何连接条件

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

自然连接表面上看起来也无须指定连接条件，但自然连接是有连接条件的，自然连接会以两个表中的同名列作为连接条件；如果两个表中没有同名列，则自然连接与交叉连接效果完全一样，因为没有连接条件。

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

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

select s.*, teacher_name
# SQL 99多表连接查询的from后只有一个表名
from student_table s 
# join连接另一个表
join teacher_table t
using(teacher_id);
on子句连接

这是最常用的连接方式，SQL 99语法的连接条件放在on子句中指定，而且每个on子句只指定一个连接条件。这意味着：如果需要进行N表连接，则需要有N-1个join…on对

select s.*, teacher_name
from student_table s
join teacher_table t
# 使用on来指定连接条件
on s.java_teacher = t.teacher_id;

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

select s.*, teacher_name
from student_table s
join teacher_table t
# 使用on来指定连接条件：非等值连接
on s.java_teacher > t.teacher_id;
左、右、全外连接

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

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

select s.*, teacher_name
from student_table s
right join teacher_table t
# 使用on来指定连接条件：非等值连接
on s.java_teacher < t.teacher_id;

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

select s.*, teacher_name
from student_table s
# left join 左外连接另一个表
left join teacher_table t
# 使用on来指定连接条件：非等值连接
on s.java_teacher > t.teacher_id;

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

SQL 99的全外连接会把两个表中所有不满足连接条件的记录全部列出，但MySQL不支持全外连接

子查询

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

  1. 出现在from语句后当成数据表，这种用法也被称为行内视图，因为该子查询的实质就是一个临时视图。
  2. 出现在where条件后作为过滤条件的值。

下面的SQL语句示范了把子查询当成数据表的用法

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

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

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

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

如果子查询返回多个值，则需要使用 in 、any和all等关键字，in可以单独使用，此时可以把子查询返回的多个值当成一个值列表

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

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

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

<any只要小于值列表中的最大值即可，>any只要大于值列表中的最小值即可。<all要求小于值列表中的最小值，>all要求大于值列表中的最大值

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

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