join on 和 left join on的区别，他们和直接两张表关联查询(使用，连接)的区别

1、背景:

        在数据库多表查询数据的时候发现问题，使用（,）连接,查询的结果竟然少很多，使用left join on就没问题。所以就仔细研究了一下。

        JOIN ON、LEFT JOIN ON 和直接使用逗号连接表（隐式内连接）是 SQL 中三种常见的表关联方式，它们在语法、功能和适用场景上有所不同。以下从定义、语法、功能、适用场景和示例等方面进行详细对比。

1. 定义和功能

（1）JOIN ON（内连接）

• 定义：JOIN ON 默认是内连接（INNER JOIN），它返回两个表中满足连接条件的记录。如果某条记录在任一表中没有匹配项，则该记录不会出现在结果集中。
• 功能：强调数据的精确匹配，适用于需要严格匹配的场景。

（2）LEFT JOIN ON（左连接）

• 定义：LEFT JOIN ON 返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的行，则右表的列将填充为 NULL。
• 功能：保留左表的完整性，即使右表中没有匹配的记录，也能保证左表的记录出现在结果集中。

（3）逗号连接（隐式内连接）

• 定义：逗号连接是早期 SQL 语法中的一种隐式内连接方式，通过逗号分隔表名，并在 WHERE 子句中指定连接条件。
• 功能：类似于内连接，返回两个表中满足连接条件的记录。如果某条记录在任一表中没有匹配项，则该记录不会出现在结果集中。

2. 语法结构

（1）JOIN ON（内连接）

SELECT column1, column2, ...
FROM table1 JOIN table2
ON table1.commoncolumn = table2.commoncolumn;

（2）LEFT JOIN ON（左连接）

SELECT column1, column2, ...
FROM table1 LEFT JOIN table2
ON table1.commoncolumn = table2.commoncolumn;

（3）逗号连接（隐式内连接）

SELECT column1, column2, ...
FROM table1, table2
WHERE table1.commoncolumn = table2.commoncolumn;

3. 功能对比

对比维度	JOIN ON（内连接）	LEFT JOIN ON（左连接）	逗号连接（隐式内连接）
结果集范围	仅返回满足连接条件的记录	返回左表所有记录，右表不匹配时为 NULL	仅返回满足连接条件的记录
左表完整性	不保留左表完整性	保留左表完整性	不保留左表完整性
右表完整性	不保留右表完整性	不保留右表完整性	不保留右表完整性
连接条件位置	在 ON 子句中指定	在 ON 子句中指定	在 WHERE 子句中指定
性能优化	优化器更易理解连接逻辑	可能更消耗资源，建议右表创建索引	优化器较难理解，可能影响性能

 

4. 适用场景

（1）JOIN ON（内连接）

• 适用场景：需要两个表中的数据严格匹配时，例如查询两个表中都存在的记录。
• 示例：查询有部门的员工信息。

  SELECT e.name, d.department_name
  FROM employees e JOIN departments d
  ON e.department_id = d.id;

（2）LEFT JOIN ON（左连接）

• 适用场景：需要保留左表完整性时，例如查询所有员工信息，包括没有部门的员工。
• 示例：查询所有员工及其部门信息，没有部门的员工部门字段为 NULL。

  SELECT e.name, d.department_name
  FROM employees e LEFT JOIN departments d
  ON e.department_id = d.id;

（3）逗号连接（隐式内连接）

• 适用场景：适用于简单查询，但现代 SQL 建议避免使用，因为它可能降低可读性和性能。
• 示例：查询有部门的员工信息。

  SELECT e.name, d.department_name
  FROM employees e, departments d
  WHERE e.department_id = d.id;

5、实际遇到的问题

（1）使用，连接

SELECT A.RECORDERCODE 提报单位编码,
       A.REMARK       备注,
       B.DESC1        换算单位,
       B.DESC2        换算分子,
       B.DESC3        换算分母
  FROM MDM_*****_CODE A, MDM_****_CODELIST B
 WHERE A.CODEID = B.CODEID
   AND A.DESC11 = '有效'
   AND A.CATEGORYCODE LIKE 'A01%'
   AND LENGTH(A.CATEGORYCODE) = 7;

（2）使用 left join on

SELECT A.RECORDERCODE 提报单位编码,
       A.REMARK       备注,
       B.DESC1        换算单位,
       B.DESC2        换算分子,
       B.DESC3        换算分母
  FROM MDM_***_CODE A
  LEFT JOIN MDM_****_CODELIST B
    ON A.CODEID = B.CODEID
   AND A.DESC11 = '有效'
   AND A.CATEGORYCODE LIKE 'A28%'
   AND LENGTH(A.CATEGORYCODE) = 7;

     (3) 总结

        其实，内连接，逗号连接(隐式内连接)，对于被连接表要展示的列如果都没有值，并非NULL,是不会作为结果展示出来的。如果关联表要显示的列空值较多，还需要展示出来这种情况就需要注意了。

6. 注意事项

• 性能优化：
  • JOIN ON 和 LEFT JOIN ON 的连接条件在 ON 子句中，数据库优化器更容易理解，从而优化查询性能。
  • 逗号连接的连接条件在 WHERE 子句中，优化器较难理解，可能导致性能下降。
• 可读性和维护性：
  • JOIN ON 和 LEFT JOIN ON 的语法更清晰，易于维护。
  • 逗号连接的语法较为隐晦，特别是当连接条件复杂时，可读性较差。
• WHERE 和 ON 的区别：
  • 在 JOIN 操作中，ON 子句用于指定连接条件，WHERE 子句用于过滤最终结果集。
  • 在 LEFT JOIN 中，ON 子句的条件不会过滤左表的记录，但 WHERE 子句会过滤左表的记录。

---

7. 总结

• JOIN ON（内连接）：适用于需要精确匹配的场景，仅返回两个表中都存在的记录。
• LEFT JOIN ON（左连接）：适用于需要保留左表完整性的场景，即使右表中没有匹配项，左表的记录也会被保留。
• 逗号连接（隐式内连接）：功能上等同于内连接，但语法较为隐晦，建议使用 JOIN ON 替代以提高可读性和性能。

根据实际业务需求和查询目标选择合适的连接方式，可以显著提升查询的效率和结果集的准确性。