子查询，in运算符写子查询,all关键字,any，some关键字，相关子查询(子查询和主查询有关系)。exists运算符。select子句中的子查询.FROM子句中的子查询，此时子查询，必须起别名

/*查找所有比生菜(id=3)更贵的产品*/
SELECT *
FROM products
WHERE unit_price >/*这里我们需要得到生菜的单价*/
/*在这种情况下我们要在括号里写子查询*/

/*查找所有比生菜(id=3)更贵的产品*/
SELECT *
FROM products
WHERE unit_price >(
	SELECT unit_price
    FROM products
    WHERE product_id = 3
)
/*当sql执行这些语句时，它会评估我们的子查询*/

in运算符写子查询 

子查询返回了一列

/*查找没有被订购过的产品*/
SELECT *
FROM products
WHERE product_id NOT IN(
	SELECT DISTINCT product_id
	FROM order_items
)

我们也可以通过join来实现子查询的功能

/*查找没有被订购过的产品*/
SELECT *
FROM clients
LEFT JOIN invoices USING(client_id)
WHERE invoice_id IS NULL

all关键字

/*查询发票总额大于顾客3所有发票总额的发票*/
SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id = 3

 

/*查询发票总额大于顾客3所有发票总额的发票*/
-- SELECT *
-- FROM invoices
-- WHERE invoice_total > ALL (200,150,500,...)
/*如果invoice_total大于ALL括号里的所有值，那么就会返回在最终结果集里面*/

SELECT *
FROM invoices
WHERE invoice_total > ALL (
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id = 3
)

any，some

/*选择至少拥有两张发票的客户*/
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
)
/*等于这段查询返回值里的任何一个，那么就会在结果表里显示*/

相关子查询

对外查询的每一行数据，都运行一下子查询

/*选择工资超过部门平均的员工*/
SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
)

 练习

/*获取高于客户平均值的发票
对每位客户找到发票的平均额
返回高于平均额的发票*/
SELECT *
FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
)

exist运算符

/*得到有发票的客户*/
-- 子查询的方法
SELECT *
FROM clients
WHERE client_id IN (
	SELECT DISTINCT client_id
    FROM invoices
);
-- 连接方法
SELECT *
FROM clients
JOIN invoices USING(client_id);
SELECT *
FROM clients c
WHERE EXISTS (
	SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
)
/*查看顾客表里是否有符合client_id = c.client_id的行
对客户表里的每一位客户都会判断client_id = c.client_id
若成立这个客户就会在结果集中返回
与IN的区别就是，IN后的子查询会把结果返回到where子句中
当使用exist时子查询并没有给外查询返回一个结果
如果in运算符后写的子查询生成了很大的结果集，使用exist运算符能更有效率*/

 练习查询所有没被订购过的产品

SELECT *
FROM products p
WHERE NOT EXISTS (
	SELECT *
	FROM order_items
    WHERE product_id = p.product_id
);
SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id
    FROM order_items
)

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,
    name,
    SUM(invoice_total) AS total_sales,
    (select AVG(invoice_total) 
		FROM invoices) AS average,
      (SELECT SUM(invoice_total) - average) AS difference
FROM clients
LEFT JOIN invoices USING(client_id)
GROUP BY client_id;
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语句中使用子查询必须起别名，不管会不会用到这个别名*/

 

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
WHERE total_sales IS NOT NULL