SQL - 子查询

• 子查询可以写的位置：select、from、where
• 子查询可以返回一个值：一个列的一些值，一个结果集(表)
• 子查询的作用：可以作为条件判断的范围，作为判断条件，可以返回特定结果值，
• 与子查询相关：
  • in 运算符；
    • in (子查询),其中子查询返回的结果是一个结果集

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

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

    • 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 关键字
    • 表示一个值要比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

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

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

  • select 子句中的子查询

  • 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

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

    • 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