Page History

...

内部查询的执行独立于外部查询，内部查询仅执行一次，执行完毕后将结果作为外部查询的条件使用。（先执行内部查询，再执行外部查询）

示例

示例均按照MYSQL语法

标量子查询

mysql> select num,name
    -> from employee
    -> where d_id=(
    -> 　　select d_id
    -> 　　from department
    -> 　　where d_name='科技部');

mysql> SELECT  playerno,town,sex
    -> FROM  PLAYERS
    -> WHERE (town,sex) = ((SELECT town FROM PLAYERS WHERE playerno=7),
    ->                     (SELECT sex FROM PLAYERS WHERE playerno=44));

行子查询

mysql> select * from employee
    -> where d_id in
    -> 　　(select d_id from department);

列子查询

mysql> select * from employee
    -> where d_id !=any
    -> 　　(select d_id from department);

mysql> select * from employee
    -> where d_id >=all
    -> 　　(select d_id from department);

表子查询

select column1 + (select max(column2) from table2) 
from table1;

select column1, column2 
from table1 
where column1 > (select max(select column1 from table2);

行子查询

select column1, column2 
from table1 
where (column1, column2) = (select avg(column1),avg(column2) from table2);

列子查询

select column1, column2 
from table1 
where column1 in (select column1 from table2);

表子查询

select t.a + t.b 
from (select column1 as a, column2 as b from table1) tmysql> select *
    -> from COMMITTEE_MEMBERS
    -> where (begin_date,end_date) in
    -> (
    ->   select begin_date,end_date
    ->   from COMMITTEE_MEMBERS
    ->   where position='Secretary'
    -> );

相关子查询

定义

在子查询中使用到了外部查询的表中的任何列。先执行外部查询，然后执行子查询。

...