SQL语句执行顺序总结及大概示例

1. Oracle执行顺序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
带表关联时：
FROM → ON → JOIN → WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → DISTINCT → ORDER BY

-- Oracle示例
SELECT 
d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d 
ON e.dept_id = d.dept_id
WHERE e.salary > 3000
GROUP BY d.dept_name
HAVING AVG(e.salary) > 5000
ORDER BY avg_salary DESC;

---

2. Hive执行顺序

FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY

-- Hive示例
SELECT 
dept_id, COUNT(*) AS emp_count
FROM employees
WHERE salary > 4000
GROUP BY dept_id
HAVING emp_count > 5
ORDER BY emp_count DESC;

---

3. PostgreSQL执行顺序

FROM → ON → JOIN → WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET

-- PostgreSQL示例
SELECT 
d.dept_name, SUM(e.sales) AS total_sales
FROM employees e
LEFT JOIN departments d 
ON e.dept_id = d.dept_id
WHERE e.join_date > '2023-01-01'
GROUP BY d.dept_name
HAVING SUM(e.sales) > 100000
ORDER BY total_sales DESC
LIMIT 5;

---

4. MySQL执行顺序

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

-- MySQL示例
SELECT 
dept_id, MAX(salary) AS max_salary
FROM employees
WHERE status = 'active'
GROUP BY dept_id
HAVING max_salary > 8000
ORDER BY max_salary DESC
LIMIT 3;

---

关键区别说明

步骤	Oracle	Hive	PostgreSQL	MySQL
表关联	在WHERE前执行	在WHERE前执行	在WHERE前执行	在WHERE前执行
HAVING	GROUP BY之后	SELECT之后	GROUP BY之后	GROUP BY之后
LIMIT	不支持	支持	支持	支持
DISTINCT	SELECT之后	SELECT之后	SELECT之后	SELECT之后

注：所有数据库的实际物理执行可能因优化器调整顺序，但逻辑顺序遵循上述规则。