Subquery in where clause  ——where里面的子query

例子1： 让我们找到过去所有赛季，每个赛季获得过 Finals MVP的球员的名字和他所在的球队

直接的实现方法可以采用join，例如：

select distinct  b.year, b.playerID, a.firstName, a.lastName, d.tmID, d.name

from

(select bioID, firstName, lastName

from basketball_master) a

join

(select year, playerID, award

from basketball_awards_players

where award = "Finals MVP") b

join

(select  year, tmIDWinner

from basketball_series_post

where round = "F" and lgIDWinner = "NBA") c

join

(select tmID, name

from basketball_teams) d

where a.bioID = b.playerID

and b.year = c.year

and c.tmIDWinner = d.tmID

order by b.year

问题是，这个query很长，不够简洁；此外，过程当中如果出现问题，观察输出结果未必见得能发现问题。好在这个query只是输出几十年的冠军，一眼就能看出来是不是出了问题。

这个query

select * from

(select bioID, year, firstName, lastName

from basketball_awards_players , basketball_master where bioID  = playerID and  award = "Finals MVP") a

join

(select tmIDWinner, P. year  from basketball_series_post P , basketball_teams T

where P.year = T.year and P.tmIDWinner = T.tmID and P.round = "F" and P.lgIDWinner = "NBA") b

on a.year = b.year

在where clause里面的select 只能选择一个expression

例子2：找到NBA里面在同一个区的球队。

这个query可以用join来完成：

select distinct * from

(SELECT  tmID, dIvID, lgID from basketball_teams

where lgID = "NBA") a

join

(SELECT  tmID, dIvID, lgID from basketball_teams where lgID = "NBA") b

where a.tmID < b.tmID and a.dIvID = b.dIvID

但是用where clause，可以更简洁一些。

SELECT  distinct tmID, dIvID, lgID from basketball_teams T1

where lgID = "NBA"

and

T1.tmID in

( select tmID  from basketball_teams T2 where T1.dIvID = T2.dIvID)

select distinct * from

(SELECT  tmID, dIvID, lgID from basketball_teams

where lgID = "NBA") a

join

(SELECT  tmID, dIvID, lgID from basketball_teams where lgID = "NBA") b

where a.tmID < b.tmID and a.dIvID = b.dIvID

group by a.tmID

order by a.dIvID

接下来我们谈一下sub query in from clause