子查询有三种:where型子查询,from型子查询,exist子查询。
where型子查询
where子查询:指把内层的查询结果当成外层查询的比较条件。
例句:查询出最新的商品:select good_id,good_name from good where good_id = (select max(good_id) from good);--此句为子查询
select good_id,good_name from good order by good_id DESC limit 1;
from型子查询
from子查询:把内层的查询结果当成临时表,供外层sql再次查询。
例:查询每个栏目下最新的商品:
select good_id,cat_id,good_name from good where good_id in (select max(good_id) from good group by cat_id);---典型的where型子查询
select * from (select good_id,cat_id,good_name from good order by cat_id asc,good_id desc) as tmp group by cat_id;--from型子查询
exists型子查询
exists子查询:把外层的查询结果,拿到内层,看内层的查询是否成立。
典型示例:select cat_id,cat_name from category where exists (select * from good where good.cat_id = category.cat_id);
该子查询现在比较少用,已被join取代。select cat_id,cat_name from category join good ON category.cat_id = good.cat_id;