目录一、基本 JOIN 类型1. 单独一个 JOIN 等价于 INNER JOIN2. LEFT JOIN / RIGHT JOIN 用法一致3. 关联键重复导致数据翻倍二、ODPS 与 Oracle 的主要差异1. () 语法差异重要2. 半连接示例LEFT SEMI JOIN3. LEFT ANTI JOINODPS 特有三、多表 JOIN 顺序与优化四、完整示例对比场景员工表emp与部门表dept左连接获取所有员工及其部门名称。五、注意事项汇总ODPSMaxCompute的 JOIN 语法与 Oracle 高度相似但在一些细节上存在差异。以下将逐一说明。一、基本 JOIN 类型JOIN 类型ODPS 语法Oracle 语法说明内连接INNER JOINJOIN或INNER JOINJOIN或INNER JOIN两者完全一致只返回匹配的行。左外连接LEFT JOINLEFT JOIN或LEFT OUTER JOINLEFT JOIN或LEFT OUTER JOIN返回左表全部行右表无匹配则为 NULL。右外连接RIGHT JOINRIGHT JOIN或RIGHT OUTER JOINRIGHT JOIN或RIGHT OUTER JOIN返回右表全部行左表无匹配则为 NULL。全外连接FULL JOINFULL JOIN或FULL OUTER JOINFULL JOIN或FULL OUTER JOIN返回两表全部行无匹配的一侧为 NULL。交叉连接CROSS JOINCROSS JOINCROSS JOIN返回笛卡尔积。半连接 / 反连接LEFT SEMI JOIN、LEFT ANTI JOINODPS特有EXISTS/NOT EXISTS或IN/NOT INODPS 提供了专门的半连接语法性能更好。1. 单独一个JOIN等价于INNER JOIN-- ODPS 与 Oracle 一致SELECT*FROMtable_a aJOINtable_b bONa.idb.id;-- 等价于SELECT*FROMtable_a aINNERJOINtable_b bONa.idb.id;2.LEFT JOIN/RIGHT JOIN用法一致-- 左连接保留左表所有记录SELECTa.id,a.name,b.order_dateFROMcustomers aLEFTJOINorders bONa.idb.cust_id;3. 关联键重复导致数据翻倍无论 ODPS 还是 Oracle只要 JOIN 的关联键在任一侧存在重复值结果行数都可能翻倍相当于一对多或多对多。-- 左表id1 出现两次-- 右表id1 出现三次-- 结果会产生 2×3 6 行 id1 的记录SELECT*FROMleft_table aJOINright_table bONa.idb.id;注意若需避免数据翻倍可提前去重或使用DISTINCT、聚合函数等。二、ODPS 与 Oracle 的主要差异差异点ODPSOracle旧式外连接语法()不支持()语法支持WHERE a.id b.id()表示左外连接半连接语法LEFT SEMI JOIN/LEFT ANTI JOIN无直接等价语法需用EXISTS/NOT EXISTSJOIN 条件写法必须使用ON子句不支持USING支持USING(column)简化相同列名关联NULL 比较行为标准 SQLNULL NULL结果为NULL不匹配相同MAPJOIN 提示支持/* MAPJOIN(small_table) */优化小表支持/* USE_HASH(table) */等不同提示1.()语法差异重要Oracle 允许在WHERE中使用()表示外连接ODPS 不支持必须使用标准的LEFT/RIGHT/FULL JOIN。-- Oracle 旧式语法不推荐但可用SELECT*FROMemp e,dept dWHEREe.dept_idd.dept_id();-- 左外连接-- ODPS 必须改写为SELECT*FROMemp eLEFTJOINdept dONe.dept_idd.dept_id;2. 半连接示例LEFT SEMI JOINODPS 的LEFT SEMI JOIN只返回左表中与右表匹配的记录且不返回右表任何列类似EXISTS。-- ODPS查询有订单的客户不关心订单详情SELECTa.id,a.nameFROMcustomers aLEFTSEMIJOINorders bONa.idb.cust_id;-- Oracle 等价写法SELECTa.id,a.nameFROMcustomers aWHEREEXISTS(SELECT1FROMorders bWHEREa.idb.cust_id);3.LEFT ANTI JOINODPS 特有返回左表中没有匹配右表的记录类似NOT EXISTS。-- ODPS查询从未下过订单的客户SELECTa.id,a.nameFROMcustomers aLEFTANTIJOINorders bONa.idb.cust_id;三、多表 JOIN 顺序与优化ODPS优化器会自动重排 JOIN 顺序但可通过/* MAPJOIN(table) */强制将小表加载到内存。Oracle优化器基于统计信息选择最优顺序也可使用ORDERED提示强制顺序。MAPJOIN 示例ODPSSELECT/* MAPJOIN(dim_date) */*FROMfact_sales aJOINdim_date bONa.date_keyb.date_key;四、完整示例对比场景员工表emp与部门表dept左连接获取所有员工及其部门名称。ODPSSELECTe.empno,e.ename,d.dnameFROMemp eLEFTJOINdept dONe.deptnod.deptno;Oracle标准语法相同也支持旧式-- 标准语法推荐SELECTe.empno,e.ename,d.dnameFROMemp eLEFTJOINdept dONe.deptnod.deptno;-- 旧式语法仅 OracleSELECTe.empno,e.ename,d.dnameFROMemp e,dept dWHEREe.deptnod.deptno();五、注意事项汇总关联键重复导致数据膨胀这是 SQL JOIN 的通用行为并非 ODPS 或 Oracle 特有。务必检查关联键的唯一性。NULL 值处理NULL与任何值包括NULL比较均为NULL导致不匹配。若需将NULL视为相等需使用NVL或COALESCE转换。性能优化大表 JOIN 时ODPS 中常用MAPJOIN提示Oracle 中则常用USE_HASH或USE_NL。字符串类型隐式转换ODPS 对隐式转换限制较严建议显式转换类型Oracle 较宽松但可能引发索引失效。分区表 JOINODPS 中 JOIN 前尽量裁剪分区WHERE partition_col ...可大幅减少数据扫描量。ODPS 的 JOIN 核心功能与 Oracle 保持一致只需注意()旧语法不支持、以及半连接语法的差异即可轻松迁移。建议统一使用标准 SQL 的JOIN ... ON语法。