更详细地解释JOIN与LEFT JOIN和WHERE条件性能建议

Question

在这个候选答案中，有人断言，在某些涉及一些WHERE子句的情况下，JOIN优于LEFT JOIN，因为它不会混淆查询计划器，也不是“无意义的”。断言/假设是，这对任何人来说都应该是显而易见的。

请进一步解释或提供链接以供进一步阅读。

Answer 1

考虑以下示例。我们有两张桌子，部门和员工。

有些部门还没有雇员。

此查询使用查找部门雇员999工作的内连接（如果有），否则它不显示任何内容（甚至不显示雇员或他或她的姓名）：

select a.department_id, a.department_desc, b.employee_id, b.employee_name
  from departments a
  join employees b
    on a.department_id = b.department_id
 where b.employee_id = '999'

下一个查询使用外部联接（位于部门和员工之间），并查找员工999所在的部门。但是，如果员工不在任何部门工作，它也不会显示员工的ID或姓名。这是因为WHERE子句中使用了外部联接表。如果没有匹配的部门，则为空（不是999，即使员工中存在999）。

select a.department_id, a.department_desc, b.employee_id, b.employee_name
  from departments a
  left join employees b
    on a.department_id = b.department_id
 where b.employee_id = '999'

但请考虑以下问题：

select a.department_id, a.department_desc, b.employee_id, b.employee_name
  from departments a
  left join employees b
    on a.department_id = b.department_id
   and b.employee_id= '999'

现在标准在on子句中。因此，即使该员工不在任何部门工作，他仍然会被退回（他的ID和姓名）。部门列将为空，但我们会得到一个结果（员工端）。

您可能认为永远不会希望在WHERE子句中使用外部联接表，但情况并非如此。但出于上述原因，通常情况下是这样的。

假设您希望所有部门都没有员工。然后可以运行以下操作，该操作使用外部联接，并且外部联接表在where子句中使用：

select a.department_id, a.department_desc, b.employee_id
  from departments a
  left join employees b
    on a.department_id = b.department_id
 where b.employee_id is null

^^显示没有员工的部门。

以上可能是您希望在WHERE子句中使用外部联接表而不是ON子句的唯一合理原因（我想这就是您的问题所在；内部联接和外部联接之间的区别是一个完全不同的主题）。

一个好的方法是这样看：您使用外部连接来允许空值。那么您为什么要使用外部连接并说一个字段不应该为空并且应该等于“XYZ”？如果一个值必须是“XYZ”（不是null），那么为什么要指示数据库允许空值返回？这就像说一件事，然后稍后覆盖它。

Answer 2

实际上，在PostgreSQL中，条件和连接的[内部]连接的条件是100%等效的。（不过，最好使用显式的连接条件，使查询更易于阅读和维护）。

对于左联接与联接右侧表上的WHERE条件组合的情况，情况并非如此。左联接的目的是保留联接左侧的所有行，而不考虑右侧的匹配。如果未找到匹配项，则使用右侧列的值扩展该行。手册：

左外连接

首先，执行内连接。然后，对于T1中与T2中的任何行不满足连接条件的每一行，在T2的列中添加一个具有空值的连接行。因此，连接表始终为T1中的每一行至少有一行。

然后，如果您在右侧的表列上应用一个WHERE条件，该条件需要NULL值以外的其他内容，则您将使效果无效并强制转换LEFT[OUTER]JOIN像普通[INNER]JOIN一样工作，只是（可能）由于更复杂的查询计划而更昂贵。

在一个有许多连接表的查询中，Postgres（或任何RDBMS）很难找到最好的（甚至是好的）查询计划。理论上可能连接表的序列数按阶乘增长（！）。Postgres对任务使用“通用查询优化器”，并且有一些设置可以影响它。

如前所述，使用误导性的左连接混淆查询会使查询规划器的工作更加困难，对人类读者来说是误导性的，并且通常会提示查询逻辑中的错误。

由此产生的问题的相关答案：

• 为什么在WHERE中null等于整数？
• 查询LEFT JOIN不返回计数为0的行
• 使用外部连接和每个父级的限制子记录进行SQL查询
• 左外连接像内连接
• 选择其他表中不存在的行

等等。