mysql中INNER JOIN与OUTER JOIN的语法使用

INNER JOIN必须带ON条件，否则报错；LEFT JOIN需将过滤条件放ON中以避免变INNER效果；ON控制关联，WHERE控制最终筛选。

INNER JOIN 只保留匹配的行，不写 ON 条件会变成笛卡尔积

INNER JOIN 要求左右表在 ON 子句中明确指定关联条件，否则 mysql 会报错 Error 1064（语法错误）或直接拒绝执行。它不会像旧式逗号语法那样默认做全连接——但如果你漏写 ON，MySQL 8.0+ 会直接报错，而低版本可能允许但结果不可控。

常见误写：

SELECT * FROM orders INNER JOIN customers;

这在大多数现代 MySQL 版本下会报错。正确写法必须带 ON：

SELECT * FROM orders  INNER JOIN customers ON orders.customer_id = customers.id;

• INNER JOIN 等价于 J OIN（关键字 INNER 可省略）
• 关联字段类型最好一致，否则可能触发隐式转换，导致索引失效
• 如果关联字段有 NULL 值，这些行一定不会出现在结果中——这是 INNER 的本质行为，不是 bug

LEFT OUTER JOIN 保留左表全部，右表缺失部分补 NULL

LEFT OUTER JOIN 中的 OUTER 关键字可省略，LEFT JOIN 就是标准写法。关键点在于：左表每行都出现一次，右表没匹配上的字段全为 NULL。

典型陷阱是把过滤条件错放 WHERE 子句：

SELECT * FROM customers  LEFT JOIN orders ON customers.id = orders.customer_id  WHERE orders.status = 'shipped';

这段 SQL 实际上把 LEFT JOIN 变成了 INNER JOIN 效果，因为 WHERE 会筛掉所有 orders.status 为 NULL 的行（即客户无订单的情况）。正确做法是把条件移到 ON 后面：

SELECT * FROM customers  LEFT JOIN orders ON customers.id = orders.customer_id AND orders.status = 'shipped';

• RIGHT OUTER JOIN 用得极少，逻辑完全可由 LEFT JOIN 调换表序替代
• FULL OUTER JOIN MySQL 原生不支持，需用 union LEFT + RIGHT 模拟
• 当左表有重复关联字段值时，结果行数可能远大于左表行数（一对多）

ON 和 WHERE 的执行顺序决定 NULL 是否被过滤

JOIN 过程分两步：先按 ON 条件生成中间临时结果集，再用 WHERE 对这个结果集做最终筛选。这意味着：

• ON 中的条件控制“哪些右表行能被拉进来”
• WHERE 中的条件控制“最终结果里保留哪些行”，此时右表字段若为 NULL，= 判断恒为 false（注意 NULL = 'x' 不成立）
• 想保留左表所有行并只取右表特定状态的记录，必须把状态条件写进 ON，不能放 WHERE

一个直观对比：

-- ✅ 保留所有客户，只连 status='shipped' 的订单（没 shipped 订单的客户仍显示） SELECT c.name, o.order_no  FROM customers c  LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'shipped'; 
-- ❌ 实际等效于 INNER JOIN，丢失无 shipped 订单的客户 SELECT c.name, o.order_no  FROM customers c  LEFT JOIN orders o ON c.id = o.customer_id  WHERE o.status = 'shipped';

性能差异主要来自驱动表选择和索引覆盖

MySQL 优化器通常会自动选小表作驱动表，但复杂 JOIN 或数据倾斜时可能出错。是否走索引、是否需要临时表/文件排序，比 JOIN 类型本身影响更大。

• 确保 ON 中的字段都有索引，尤其是右表关联字段（如 orders.customer_id）
• INNER JOIN 有时比 LEFT JOIN 更快，不是因为语法，而是因为优化器能更早剪枝（无需保留 NULL 行）
• 用 EXPLaiN 看 type 是否为 ref 或 eq_ref，避免 ALL（全表扫描）
• 大表 LEFT JOIN 小表时，若小表无索引，性能可能断崖式下跌

最易被忽略的是：即使语法完全正确，如果关联字段存在大量 NULL 或类型不一致（比如 VARCHAR 和 int 比较），也会让索引失效，这时 LEFT JOIN 的代价可能比想象中高得多。