oracle，postgre和mysql数据库JOIN操作深度解析学习

一、JOIN操作的高级对比

1. JOIN类型支持与语法差异

示例：横向连接（仅PostgreSQL支持）

-- 每篇文章关联其最新的3条评论
SELECT a.title, c.content
FROM articles a
LEFTJOINLATERAL (
    SELECT*FROM comments 
    WHERE article_id = a.id 
    ORDERBY created_at DESC
    LIMIT 3
) c ONtrue;

2. JOIN性能优化策略

Oracle： 自动选择HASH JOIN或NESTED LOOP，支持并行执行（PARALLEL提示）。

SELECT /*+ PARALLEL(e 4) */ e.name, d.dept_name
FROM employees e 
JOIN departments d ON e.dept_id = d.id;

PostgreSQL： 支持HASH JOIN和MERGE JOIN，可手动设置优化器开关（SET enable_nestloop = off;）。

MySQL： 8.0+默认使用Hash Join，但复杂查询仍可能退化为嵌套循环，需通过索引优化。

二、子查询的深度对比

1. 子查询类型与优化

示例：关联子查询优化（Oracle vs MySQL）

-- Oracle自动优化
SELECT name FROM employees e 
WHERE EXISTS (SELECT 1 FROM sales WHERE emp_id = e.id);

-- MySQL需手动改写为JOIN
SELECT e.name 
FROM employees e 
JOIN sales s ON e.id = s.emp_id 
GROUP BY e.id;

2. CTE（公共表表达式）

Oracle/PostgreSQL： 支持递归CTE，适用于层次化查询（如组织架构树）。

WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id FROM departments WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id 
    FROM departments d 
    JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

MySQL： 8.0+支持CTE，但递归深度有限（默认1000层），需调整参数cte_max_recursion_depth。

三、窗口函数与数据分析

1. 功能支持对比

示例：计算累计销售额（Oracle/PostgreSQL）

SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS rolling_7d
FROM sales;

2. 高级分析函数

Oracle： 独有的MODEL子句，支持电子表格式计算。

SELECT year, product, sales 
FROM sales_data 
MODEL 
  PARTITION BY (product) 
  DIMENSION BY (year) 
  MEASURES (sales) 
  RULES (sales[2025] = sales[2024] * 1.1);

PostgreSQL： 支持FILTER子句聚合，优化条件统计。

SELECT department,
       COUNT(*) FILTER (WHERE salary > 10000) AS high_earners
FROM employees 
GROUP BY department;

四、实战练习与选型建议

练习任务

JOIN优化对比：

• 在PostgreSQL和MySQL中执行相同多表JOIN查询，对比EXPLAIN ANALYZE结果。
• 示例查询：SELECT u.name, o.order_id, p.product_name FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id WHERE u.country = 'IT咸鱼';

窗口函数应用：

• 在Oracle或PostgreSQL中计算“每个部门薪资前三名员工”。

选型决策指南

常见问题解答

1. 为什么MySQL的JOIN性能较差？
   • 早期版本优化器简单，8.0+引入Hash Join有所改善，但仍缺乏复杂查询的深度优化。
2. 如何选择CTE还是子查询？
   • 代码可读性：CTE更清晰；性能：Oracle/PostgreSQL中CTE可缓存，MySQL中可能被多次执行。
3. 性能监控：使用工具（如Oracle的AWR、PG的pg_stat_statements）分析慢查询。
4. 延伸阅读：
   • Oracle高级查询优化：《Oracle性能诊断艺术》
   • PostgreSQL窗口函数：https://www.postgresqltutorial.com/
   • MySQL 8.0新特性：https://dev.mysql.com/doc/refman/8.0/en/

通过本日学习，您已掌握三种数据库在高级查询中的核心差异。第三天我们将深入探讨索引优化、锁机制与并发控制的对比！