Where型子查询
指把内层查询结果作为外层的比较条件
典型的查询是查找最贵的商品,比如:select name,price from goods where price = (select max(price) from goods)
需要注意的是,如果这个子查询返回单列数据,而内容有多条,则应用in(可以看作set)比如查找每个商品类型中最贵的商品(如果每个商品的价格都不一样) select name,price,type from goods where price in (select max(price) from goods group by type)
from型子查询
指将内层的查询结果当成一张表,供外层继续查询,注意内层的表要加别名,比如查找每个商品类型中最贵的商品select name,price,type from (select name ,price,type from goods order by price desc) as tmp group by type
exist型子查询
指把外层子查询的结果拿到内层的子查询测试,如果该结果在内层查询中没有查到,则过滤掉该结果,比如查询没有上架的商品类型select type,type_name from types where not exist (select * from goods where goods.type = types.type)
这样查询的效率比用where...in
要高