6. 复杂查询

编写子查询

use sql_store;
-- Find products that are more
-- expensive than Lettuce ( id = 3)
-- 编写子查询
select *
from products
where name like 'Lettuce%';
    
select *
from products
where unit_price > (
	select unit_price
	from products
	where name like 'Lettuce%'
)

-- In sql_hr database:
-- Find employees whose earn more than average

use sql_hr;

select 
	avg(salary) as avergae
from employees;
-- 没有结果
select *
from employees
having salary > avg(salary);
-- 正确答案
select *
from employees
where salary > (
	select 
	avg(salary)
	from employees
	)

注意：having 中不可以再使用属性名比较，只能使用聚合函数判断；同样的道理，where 反过来

需要确定子查询是返回的唯一值才可以继续，如果一个集合，可以考虑用 in

in运算符

use sql_store;
-- Find the products that have never been ordered

select *
from products
where product_id not in (
	select 
		distinct product_id
	from order_items
)

使用 in 运算符，进行子查询。

use sql_invoicing;

select *
from clients
where client_id not in(
	select distinct client_id
    from invoices
)

子查询和连接

子查询（Subquery）是将一张表的查询结果作为另一张表的查询依据并层层嵌套，其实也可以先将这些表链接（Join）合并成一个包含所需全部信息的详情表再直接在详情表里筛选查询。 两种方法一般是可互换的，具体用哪一种取决于 效率/性能（Performance） 和 可读性（readability）。

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;

use sql_store;
select 
	customer_id, 
    first_name,
    last_name
from customers c
where c.customer_id in (
	select customer_id
    from orders o
    where o.order_id in (
		select order_id
		from order_items oi
		where oi.product_id = (
			select product_id
			from products p
			where p.name like 'Lettuce%'
		)
    )
)

-- 上面是使用嵌套的方法，同样可以写出连接的方法，这里就不写了

这个案例中，先将所需信息所在的几张表全部连接合并成一张大表再来查询筛选明显比层层嵌套的多重子查询更加清晰明了

all关键字

use sql_invoicing;

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

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

上面的子查询返回了一列值 使用ALL会和子查询里面返回的所有值进行比较，父查询如果存在满足所有子查询条件，返回这条记录。 等价于在子查询中 max()

any关键字

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 = any(
	select client_id
	from invoices
	group by client_id
	having count(*) >= 2
);

如果说 all 是与运算，那么 any 就是或运算 = any等价于in

• ANY/SOME (……) 与 > (MIN (……)) 等效

• = ANY/SOME (……) 与 IN (……) 等效

相关子查询

-- Select employees whose salary is
-- above the average in their office
use sql_hr;

select *
from employees e
where salary > (
	select avg(salary)
    from employees
    where office_id = e.office_id
)

如上面样例中的类似，如果在子查询中出现了父查询表的别名，就可以称为相关子查询。 没有则是不相关子查询。

注意：使用相关子查询，这段查询会在主查询的每一行的层面执行，所以相关子查询会很慢

-- Get invoices that are larger than the
-- client's average invoice amount
use sql_invoicing;

select *
from invoices i
where invoice_total > (
	select
		avg(invoice_total) as average
	from invoices
    where client_id = i.client_id
);

exists 运算符

IN + 子查询 等效于 EXIST + 相关子查询 如果前者子查询的结果集过大占用内存，用后者逐条验证更有效率。 另外 EXIST() 本质上是根据是否为空返回 TRUE 和 FALSE，所以也可以加 NOT 取反。

-- Select clients that have an invoice

select *
from clients
where client_id in (		-- in (1, 2, 3, 5)
	select distinct client_id
    from invoices
);

-- 使用exist
select *
from clients c
where exists (
	select client_id
    from invoices
    where client_id = c.client_id
)

**注意： 上面这个代码先执行子查询 in ( )，并把查询结果返回到 where 子句。 如果返回的结果非常多，会妨碍最佳性能

使用 exists 可以优化这个查询中的性能 子查询并没有真的把结果集返回给外查询**

-- Find the products that have never been ordered

use sql_store;

select *
from products p
where not exists(
	select *
    from order_items
    where product_id = p.product_id
)

只写 select * 就可以了，不需要选具体的属性。

select子句中的子查询

select
	invoice_id,
    invoice_total,
    (select avg(invoice_total)
		from invoices) as invoice_average,
	invoice_total - (select invoice_average) as difference
from invoices

简单讲就是，SELECT选择语句是用来确定查询结果选择包含哪些字段 每个字段都可以是一个表达式，而每个字段表达式里的元素除了可以是原始的列，具体的数值，也同样可以是其它各种花里胡哨的子查询的结果

select
	client_id,
    c.name,
    (select sum(invoice_total)
		from invoices i
        where i.client_id = c.client_id) as total_sales,
	(select avg(invoice_total)
		from invoices i) as average,
	(select total_sales) - (select average) as difference 
from clients c;

很多奇特的用法：

• (select total_sales) - (select average) as difference 这句可以直接写为(select total_sales - average) 可以理解为两个子查询在同一张表里面，可以直接减掉。
• 子查询的使用了相关子查询，没有使用join连接，更优。原理可以看上面的相关子查询。

from子句中的子查询

USE sql_invoicing;

select *
from (
	-- 每当我们在form子句中使用子查询，必须要给子查询的结果一个别名
	select
		client_id,
		c.name,
		(select sum(invoice_total)
			from invoices i
			where i.client_id = c.client_id) as total_sales,
		(select avg(invoice_total)
			from invoices i) as average,
		(select total_sales) - (select average) as difference 
	from clients c
) as sales_summary
where total_sales is not null

很好理解，子查询当成一张表再次查询。 需要注意的是，每当我们在form子句中使用子查询，必须要给子查询的结果一个别名。

from中的子查询会让查询变得复杂，但是可以使用视图（views）来存储这张表，可以大大简化sql语句的复杂。