（四）复杂查询

前面我们讲的都是简单的查询，也就是只包含一条 SELECT 语句，并且只查询一张表的数据。SQL 允许我们进行更复杂的查询，也就是我们今天要讲的复杂查询。它包括：连接查询，联合查询和子查询。

一、连接查询

在设计表的时候，为了避免数据的冗余，我们往往会将数据分散到多个表中。因此，在我们查询数据的时候，需要连接多个表进行查询。 SQL92 和 SQL99 连表查询的语法有很大的不同。建议大家采用 SQL99 标准，因为它的层次性更强，可读性也更高。我们也以 SQL99 标准进行讲解。 比较常见的连接查询有：交叉连接，等值连接，非等值连接，外连接和自连接。接下来，我们就分别讲解下这 5 种连接方式。

1.交叉连接 交叉连接也叫笛卡尔乘积。那什么是笛卡尔乘积呢？wikipedia 对笛卡尔乘积的定义如下： 在数学中，两个集合X和Y的笛卡儿积（英语：Cartesian product），又称直积，在集合论中表示为X x Y ，是所有可能的有序对组成的集合，其中有序对的第一个对象是X的成员，第二个对象是Y的成员。

# X = {1, 2} # Y = {a, b, c} # X x Y = {(1, a),(1, b),(1, c),(2, a),(2, b),(2, c)}

在 SQL99 中，我们可以通过 CROSS JOIN 获取多张表的笛卡尔乘积。

select * from player cross join team;

2.等值连接 等值连接就是对多张表中相同的字段进行等值判断。在 SQL99 中可以有多种方式表示等值连接。 自然连接 NATURAL JOIN 会自动帮你查询两张连接表中所有相同的字段，然后进行等值连接。

select * from player natural join team;

USING 连接 当然我们还可以用 USING 来指定用哪些同名字段进行等值连接。

select * from player join team using(team_id);

ON 连接 ON 表示我们想要连接的条件，我们也可以用 ON 来实现等值连接。（可以是非同名字段进行等值连接）

select * from player join team on player.team_id = team.team_id;

3.非等值连接 连接两张表的条件如果是相等判断，那就是等值连接，否则就是非等值连接。 比如说：我们想查询每个球员的身高级别。

select player_id, player_name, height, height_level from player join height_grades on height between height_lowest and height_highest;

4.外连接 外连接除了查询满足条件的记录以外，还可以查询某一方不满足条件的记录。两张表做外连接，会有一张表是主表，另一张表是从表。 左外连接 左外连接，就是左边的表是主表，需要显示左边表的全部行。右边表是从表，只显示满足条件的行。关键字为 LEFT OUTER JOIN 。

create table t_boys( bid int primary key, name varchar(255) not null, gid int ); insert into t_boys values (1, 'Aden', 1); insert into t_boys values (2, 'Bob', 2); insert into t_boys values (3, 'Clark', 100); insert into t_boys values (4, 'David', null); create table t_girls( gid int primary key, name varchar(255) not null, bid int ); insert into t_girls values (1, 'Allen', 1); insert into t_girls values (2, 'Beyonce', 2); insert into t_girls values (3, 'Cindy', 100); insert into t_girls values (4, 'Diana', null); # 左外连接 left [outer] join # 查询所有女生的恋爱情况 # select * from t_girls join t_boys using(bid); select * from t_girls left join t_boys using(bid);

右外连接 右外连接，就是右边的表是主表，需要显示右边表的全部行。左边表是从表，只显示满足条件的行。关键字为 RIGHT OUTER JOIN 。

# 右外连接 right [outer] join # 查询所有男生的恋爱情况 select * from t_girls right join t_boys using(gid);

全外连接 两张表都是主表，都需要显示全部行。但是MySQL不支持全外连接。关键字为 FULL OUTER JOIN 。

# 全外连接 full [outer] join # 查询所有男生和女生的恋爱情况 select * from t_girls full join t_boys using(bid); # 注意事项：MySQL不支持全外连接

5.自连接 我们可以连接不同的表，也可以对同一张表进行连接，这样的连接我们称之为自连接。 比如我们想要查看比布雷克-格里芬高的球员都有谁？

select t1.player_name, t1.height from player as t1 join player as t2 on t2.player_name = '布雷克-格里芬' and t1.height > t2.height;

二、联合查询

我们可以用 UNION 关键字，将多个结果集合并成一个结果集，这样的查询我们叫联合查询。 应用场景： 要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时。 注意事项: a. 列数一致 b. 对应的数据最好一致 c. UNION会去重, UNION ALL不会去重

# 2. 联合查询 # 练习：查询所有男生和女生的信息 insert into t_girls values (5, 'Eye', null); insert into t_boys values (5, 'Eye', null); select * from t_girls; select * from t_boys; select * from t_girls # 主查询 union select * from t_boys; # 子查询 select * from t_girls # 主查询 union all select * from t_boys; # 子查询 # 注意事项：union会去重,union不会去重

三、子查询

子查询其实就是嵌套在查询中的查询。这样做的好处是：我们可以进行更加复杂的查询，更容易理解查 询的过程。很多情况下，我们无法直接从数据表中得到我们想要的结果。往往需要先进行一次查询，然 后在这次查询的基础上，再次进行查询。

子查询可以分为关联子查询和非关联子查询。

如果子查询只执行一次，然后子查询的结果集会作为外部查询的条件进行判断，那么这样的子查询叫做 非关联子查询。 比如：我们想要查询哪个球员的身高最高，最高身高是多少？

select player_name,height from player where height = ( select max(height) from player # 该子查询称之为标量子查询或者非关联子查询 );

如果子查询依赖于外部查询，通常情况下是因为子查询用到了外部查询的某些字段。因此，每执行一次 外部查询，子查询都要重新执行一次，这样的子查询叫做关联子查询。 比如：我们想要查询每个球队中 大于平均身高的球员有哪些，并显示球员姓名，身高以及所在球队 ID。

select player_name, height, team_id from player as t1 where height > ( select avg(height) from player as t2 where t2.team_id = t1.team_id # 关联子查询 );

1.EXISTS 子查询 关联子查询可能会搭配 EXISTS 关键字一起使用。 EXISTS 用来判断子查询的结果集是否为空集。如果 不为空集返回 True ，如果为空集返回 False 。 举个列子：查询出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID。

select player_id, player_name, team_id from player where exists( select player_id from player_score where player_id = player.player_id );

那么， NOT EXISTS 自然就是不存在的意思。 比如：查询没出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID。

select player_id, player_name,team_id from player where not exists( select player_id from player_score where player_score.player_id = player.player_id );

2.集合比较子查询 集合比较子查询的作用是与外部查询的结果集进行比较。主要有以下几个关键字：IN, SOME (ANY),ALL。他们的含义如下： 还是上面那个例子：查询出过场的球员都有哪些，并显示他们的球员ID，球员姓名，球队ID。我们可以采用 IN 来进行操作。

select player_id, player_name,team_id from player where player_id in( select distinct player_id from player_score );

了解了 IN 关键字后，我们接下来看下 SOME 和 ALL 。它们都需要和比较操作符一起使用，这些比较操作符包括： > , = , < , >= , <= , 和 <> 。 举个例子：我们要查询其他球队中比印第安纳步行者 (team_id=1002) 中某个球员身高高的球员有哪些，显示它们的球员ID，球员姓名和球员身高。

select player_id, player_name, height from player where team_id != 1002 and height > some( select height from player where team_id = 1002 ); # 等价于 select player_id, player_name, height from player where team_id != 1002 and height > ( select min(height) from player where team_id = 1002 );

同样，如果我们想查询比印第安纳步行者 (team_id=1002) 中所有球员身高都高的球员有哪些，显示它们的球员ID，球员姓名和球员身高。

select player_id, player_name, height from player where team_id != 1002 and height > ALL( select height from player where team_id = 1002 ); # 等价于 select player_id, player_name, height from player where team_id != 1002 and height > ( select max(height) from player where team_id = 1002 );

最后，再强调下 SOME 和 ALL 必须要与一个比较操作符一起使用，不然起不到集合比较的作用。

3.子查询作为计算字段 子查询甚至可以作为计算字段存在。举个例子：查询每个球队的名称，和它们的球员数。

# 方式一 select *, (select count(*) from player where player.team_id = team.team_id ) as teanm_nums from team; # 方式二 select team_name,team_nums from team left join ( select team_id,count(*) as team_nums from player group by team_id ) as t using(team_id);

我们可以发现：子查询可以作为计算字段存在。我们通常会给这个计算字段起个别名，因为子查询实在太长，别名更容易理解。

#个人学习记录，如发现有错误之处，欢迎与我交流