SQL - 子查询

• in (子查询),其中子查询返回的结果是一个结果集

• select *
  from clients
  where client_id in (
  select client_id
  from invoices
  group by client_id
  having count(*)>2)

• 子查询和连接，都能用来查询信息，比较的是可读性和查询效率

• select *
  from clients
  where client_id not in (
  select  distinct client_id 
  from invoices)
  -- 同一个问题，连接和子查询的比较
  select *
  from clients 
  left join invoices using (client_id)
  where invoice_id is null

• 表示一个值要比all修饰的子查询返回的一列值都大，或都小等；同理可得，any关键字和some关键字都是类似的含义。

• 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 *
  from employees e
  where salary >(
  select avg(salary)
  from employees 
  where office_id=e.office_id)
  -- 也可以写这样，但是体现不了相关子查询的特点
  -- select *
  -- from employees
  -- join (select office_id,avg(salary) as aaa
  -- from employees
  -- group by office_id) o
  -- on o.office_id=employees.office_id
  -- where salary>aaa

• where exists (子查询)

• 能够提高效率，在子查询的过程中，找到一行符合条件的记录就返回true

• -- exists
  select *
  from clients c
  where exists (
  select client_id
  from clients
  where client_id=c.client_id)

select invoice_id,invoice_total,(select avg(invoice_total) from invoices) as invoice_avg,invoice_total- (select invoice_avg) as defference
from invoices；select client_id,name,sum(invoice_total),
(select avg(invoice_total) from invoices) as average,
sum(invoice_total)-(select average) as difference
from clients
left join invoices using (client_id)
group by client_id,name

• 子查询的结果可以作为一张真实的表使用

• select *
  from employees
  join (select office_id,avg(salary) as aaa
  from employees
  group by office_id) o
  on o.office_id=employees.office_id
  where salary>aaa