子查询的几种方式，记笔记✍️

直接进入主题！🫏

1. 子查询在 SELECT 中

SELECT emp_name, (SELECT COUNT(*) FROM orders WHERE orders.emp_id = employees.emp_id) AS order_count
FROM employees;

2. 子查询在 WHERE 子句中

2.1 IN

个人最喜欢的一种方式，很符合直觉

SELECT emp_name
FROM employees
WHERE emp_id IN (SELECT emp_id FROM orders WHERE order_date > '2024-01-01');

2.2 比较运算符

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

EXISTS 子句

SELECT emp_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id);

子查询在 FROM 子句中

也就是虚拟表，很好用

SELECT e.emp_name, o.order_count
FROM employees e
JOIN (SELECT emp_id, COUNT(*) AS order_count FROM orders GROUP BY emp_id) o
ON e.emp_id = o.emp_id;

子查询在 HAVING 子句中

前面那些就够了，这个用不多

SELECT emp_id, COUNT(*) AS order_count
FROM orders
GROUP BY emp_id
HAVING COUNT(*) > (SELECT AVG(order_count) FROM (SELECT COUNT(*) AS order_count FROM orders GROUP BY emp_id) AS sub);

最后

IN和FROM配合子查询很好用！！！ 下次见了 准备散散步去了🚶‍♂️