子查询语句(SubQuery)

## 薪水大于平均薪水的雇员
select * from employees
where salary > (select avg(salary) from employees);

IN 操作符 in 子查询

## 没有发票的顾客
select * from clients 
where client_id not in(select distinct client_id from invoices)

子查询 与 join

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

## 也是查询没有发票的顾客
## 和使用IN操作符+子查询语句表达的意思是一样的
## 但对于表达同样意思的不同于据来说,既要考虑语句的性能,也要考虑语句的可读性

ALL 关键字 in 子查询

## 比client id为3的所有发票都大的发票
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
)
## 每一条invoice记录都会和所有的子句返回的值进行比较,返回大于所有值的结果

ANY / SOME 关键字 in 子查询

## 至少有两个发票的顾客
select * from clients 
where client_id in (
	select client_id from invoices
	group by client_id
	having count(*) >= 2
)

## = any 相当于 in
select * from clients 
where client_id = any (
	select client_id from invoices
	group by client_id
	having count(*) >= 2
)

相关子查询(Correlated SubQuery)

-- 对于 employees 中的每个记录e
	-- 计算employees中满足office_id=e.office_id条件的avg salary
        -- 如果e.salary > avg salary 返回记录

-- 在这中情况下 子查询会在每次主查询的每条记录执行一次 所以性能会有所降低
select * from employees e
where salary > (
	select avg(salary) from employees
    where office_id = e.office_id
	-- 在这个子查询中,有个条件是依赖外部查询的 
)

EXISTS 操作符

## 有发票的顾客
select * from clients c
where exists (	-- 表达的意思和in操作符一样,只不过当in()中的结果集很大时,会有负面的性能开销;使用exists相关子查询更好
	select client_id from invoices
	where client_id = c.client_id -- 相关子查询
)

SELECT子句中的子查询

select 
	invoice_id,
	invoice_total,
    (select avg(invoice_total) from invoices) as invoice_average,
    invoice_total - (select invoice_average) as difference -- 不能写成 invoice_total - invoice_average,因为不能在表达式中使用别名
from invoices

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

FROM子句中的子查询

select * 
from (
	select 
		client_id,
		name,
		(select sum(invoice_total) from invoices
			where client_id = c.client_id) as total_sales,
		(select avg(invoice_total) from invoices) as average,
		(select total_sales - average) as difference
	from clients c
) as sales_summary -- FROM子句中子查询的结果必须要有别名
where total_sales is not null