学习MySQL子查询与多表联查

子查询

子查询是嵌套在其他SQL查询中的查询，通常用于在WHERE、FROM或SELECT子句中提供条件或中间结果。

标量子查询（返回单个值）
常用于WHERE条件中与其他值比较：

SELECT employee_name 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

列子查询（返回一列）
结合IN、ANY等操作符使用：

SELECT product_name 
FROM products 
WHERE category_id IN (SELECT category_id FROM categories WHERE is_active = 1);

行子查询（返回一行）
通过多列条件匹配：

SELECT * 
FROM orders 
WHERE (customer_id, order_date) = (SELECT customer_id, MAX(order_date) FROM orders GROUP BY customer_id);

表子查询（返回多行多列）
通常作为临时表参与联查：

SELECT a.* 
FROM employees a 
JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) b 
ON a.department_id = b.department_id AND a.salary > b.avg_salary;

多表联查

通过JOIN操作关联多个表，分为内连接、外连接等类型。

内连接（INNER JOIN）
仅返回两表匹配的行：

SELECT a.order_id, b.customer_name 
FROM orders a 
INNER JOIN customers b ON a.customer_id = b.customer_id;

左连接（LEFT JOIN）
保留左表所有行，右表无匹配则填充NULL：

SELECT a.employee_name, b.department_name 
FROM employees a 
LEFT JOIN departments b ON a.department_id = b.department_id;

右连接（RIGHT JOIN）
保留右表所有行，左表无匹配则填充NULL：

SELECT a.product_name, b.category_name 
FROM products a 
RIGHT JOIN categories b ON a.category_id = b.category_id;

全连接（FULL JOIN）
MySQL不直接支持，需通过UNION实现：

SELECT a.*, b.* 
FROM table1 a 
LEFT JOIN table2 b ON a.id = b.id 
UNION 
SELECT a.*, b.* 
FROM table1 a 
RIGHT JOIN table2 b ON a.id = b.id 
WHERE a.id IS NULL;

交叉连接（CROSS JOIN）
返回笛卡尔积，无关联条件：

SELECT a.color, b.size 
FROM colors a 
CROSS JOIN sizes b;

---

组合应用示例

结合子查询与多表联查解决复杂问题：

SELECT a.order_id, a.amount, b.customer_name 
FROM orders a 
JOIN (SELECT customer_id, customer_name FROM customers WHERE region = 'Asia') b 
ON a.customer_id = b.customer_id 
WHERE a.amount > (SELECT AVG(amount) FROM orders);