mysql详解5：复杂查询

子查询
select *
from products
where unit_price>(select unit_price from products where id =3)

in
select * from
clients
where client_id not in (
select DISTINCT client_id FROM invoices)

子查询vs连接

---find clients without invoices (子查询见上面   )

select *
from clients
left join invoices using(client_id)
where invoice_id is null

all
select * from
invoices
where invoice_total>ALL
(select invoice_total from invoices where client_id =3)

select * from
invoices
where invoice_total>
(select MAX(invoice_total) from invoices where client_id =3)

select s1 from t1 where s1 <> any (select s1 from t2);
SOME 是any的别名
=ANY 相当于 in

相关子查询
查询超过本部门平均工资的员工信息
select *
from employees
where salary>(
select AVG(salary)
from employees
where office_id =e.office_id)

exists 比in效率更高
select *
from clients c
where exists(
select client_id
from invoices
where client_id =c.client_id
)

select语句中用子查询
select client_id,
name,
(select SUM(invoice_total) from invoices
where client_id =c.client_id as total_sales)
from clients

from 语句中使用子查询 需要给虚拟的表起别名