子查询转连接查询

不同类型子查询的转换方法

1. IN 子查询转换为 INNER JOIN

子查询示例：

SELECT * FROM user WHERE dept_id IN (SELECT dept_id FROM dept WHERE status = '0')

转换为连接查询：

SELECT u.* FROM user u INNER JOIN dept d ON u.dept_id = d.dept_id WHERE d.status = '0'

2. EXISTS 子查询转换为 LEFT JOIN

子查询示例：

SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM post p WHERE u.user_id = p.user_id AND p.status = '0')

转换为连接查询：

SELECT u.* FROM user u LEFT JOIN post p ON u.user_id = p.user_id AND p.status = '0' WHERE p.post_id IS NOT NULL

3. 相关子查询转换为 JOIN

子查询示例：

SELECT u.*, (SELECT COUNT(*) FROM post p WHERE p.user_id = u.user_id) AS post_count FROM user u

转换为连接查询：

SELECT u.*, COUNT(p.post_id) AS post_count FROM user u LEFT JOIN post p ON u.user_id = p.user_id GROUP BY u.user_id