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

總結

以上是生活随笔為你收集整理的子查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。