复杂查询

Subqueries（子查询）

子查询可以返回一个值、一个列表（结果集）、一个表

-- 此时的子查询只是返回一个值
-- 找出工资比平均值多的员工
SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
)

The IN Operator（子查询返回的是一些值的集合）

-- 子查询返回的是一些值的集合
-- 找出没有invoices的客户
SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
)

Subqueries VS Joins

-- Find customers who have ordered lettuce (id = 3)
-- 		Select customer_id, first_name, last_name

-- IN
SELECT 
	c.customer_id,
	c.first_name,
	c.last_name
FROM customers c
WHERE customer_id IN (
	SELECT o.customer_id
	FROM orders o
	JOIN order_items oi USING (order_id)
	WHERE oi.product_id = 3
)

-- Left Join:better
SELECT DISTINCT
	c.customer_id,
	c.first_name,
	c.last_name
FROM customers c
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3

The ALL Keyword（大于子查询的全部结果）

-- ALL:大于子查询的全部结果
-- Select invoices larger than all invoices of
-- client 3

SELECT *
FROM invoices
WHERE invoice_total > (
-- 这里子查询是一个值
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id = 3
)

SELECT *
FROM invoices
-- 此时选择的是:invoice_total大于后面结果集的所有结果
WHERE invoice_total > ALL (
-- 这里子查询是一个结果集
	SELECT invoice_total
	FROM invoices
	WHERE client_id = 3
)

The ANY Keyword（大于子查询的某个结果）

= ANY 等价于IN

-- 找出没有invoices的客户
SELECT *
FROM clients
WHERE client_id NOT = ANY (
    SELECT DISTINCT client_id
    FROM invoices
)

Correlated Subqueries（相关子查询：引用了外部查询的表，不跨越其他的表数据，可以用于选出表内各自的数据）

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

-- for each employee
-- 		calculate the avg salary for employee.office
-- 		return the employee if salary > avg

SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
	WHERE office_id = e.office_id
)
-- 执行流程:先执行SELECT * FROM employees
-- 			对每个员工执行子查询:每次会计算在同一个办公室员工的平均值，
-- 			如果这个员工的工资高于平均值，将会将这个员工加入结果集

-- Get invoices that are larger than the
-- client's average invoice amount
-- 找出发票金额高于每顾客平均发票金额的发票

SELECT *
FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
	FROM invoices
	WHERE client_id = i.client_id
)

The EXISTS Operator

-- IN和EXISTS的区别
-- IN返回的是将一个结果集返回过WHERE语句
-- EXISTS:子查询并没有将结果集返回给外部查询，它只给出子查询是否有满足条件的记录，(返回的是一个boolean值)
-- 然后这个EXIST操作符会将其添加到最终结果集
-- 如果需要返回的是一个巨大的结果集,那最好就用EXIST操作符

-- 找出从未被订购过的商品

SELECT *
FROM products
WHERE product_id NOT IN (
	 SELECT product_id
	 FROM order_items
)

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

Subqueries in the SELECT Clause（select语句里面使用子查询）

-- 选出每个客户的id、名字、各自的购买数额、平均值、购买数额的与平均值的差值

SELECT
	client_id,
	`name`,
	(SELECT SUM(invoice_total) 
        FROM invoices 
        WHERE client_id = clients.client_id) AS total_sales,
	(SELECT AVG(invoice_total) 
		FROM invoices) AS avarage,
	-- (SELECT total_sales) - (SELECT avarage) AS differece
	(SELECT total_sales - avarage) AS differece
FROM clients

Subqueries in the FROM Clause（将查询的表作为别的查询的FROM子句）

-- 将查询的表作为别的查询的FROM子句
SELECT *
FROM (
		SELECT
			client_id,
			`name`,
			(SELECT SUM(invoice_total) 
				FROM invoices 
				WHERE client_id = clients.client_id) AS total_sales,
			(SELECT AVG(invoice_total) 
				FROM invoices) AS avarage,
			(SELECT total_sales - avarage) AS differece
		FROM clients
) AS sales_summary
WHERE total_sales IS NOT NULL