子查询

13.7.1 转子查询为内连接

select name from country

where code in (select countrycode from countrylanguage);

1.移动CountryLanguage表到From支局

2.这where语句表这代码列和返回的country代码列比较，转换In表达式为明确的比较在country代码列。

select name from country,countryLanguage

where code =countryCode;

为了列出唯一的名字select distinct name from country,countryLanguage where code=contrycode;

13.7.2 转换子查询到外连接

select name from country where code not in(select coutrycode from countryLanguage);

1.移动countrylanguage到from语句和使用left join

2.where语句比较代码列和子查询的代码列进行比较。转换In表达式到一个明确的在country代码列在from语句

3.在where语句，限制输出到这些行有NULL

select name from country left join countrylanguage on code=contrycode where countrycode is null;

也可以转换为右外连接

select from countrylanguage right join country on countrycode =code where countrycode is null;

13.8 使用子查询在updates中

create table NACities select * from city where countryCode in (select code from country where continent ='North America');

DELETE from NACities where CountryCode IN(select code from country where lifeExpectancy <80);

注意：MYsql does not allow a table that is being updated in the outer query to be selected from in any subquery of the statement.如下：

delete from NACities where ID　IN (select ID from NACites where Population <500);You can't specify target table 'NACities' for update in FROM clause