详细解释JOIN与LEFT JOIN和WHERE条件表现建议 [英] Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail

问题描述

在此候选答案中，有人断言在某些情况下，JOIN比LEFT JOIN更好，WHERE子句，因为它不会混淆查询计划程序，并且不是毫无意义的".断言/假设是任何人都应该显而易见.

In this candidate answer it is asserted that JOIN is better than LEFT JOIN under some circumstances involving some WHERE clauses because it does not confuse the query planner and is not "pointless". The assertion/assumption is that it should be obvious to anyone.

请进一步说明或提供链接以供进一步阅读.

Please explain further or provide link(s) for further reading.

推荐答案

请考虑以下示例.我们有两个表，部门和雇员.

Consider the following example. We have two tables, DEPARTMENTS and EMPLOYEES.

某些部门还没有员工.

此查询使用内部联接，查找内部员工999所在的部门(如果有)，否则不显示任何内容(甚至不显示员工或其姓名):

This query uses an inner join that finds the department employee 999 works at, if any, otherwise it shows nothing (not even the employee or his or her name):

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子句中使用了外部联接表.如果没有匹配的部门，它将为null(即使员工中有999，也不是999).

This next query uses an outer join (left between departments and employees) and finds the department that employee 999 works for. However it too will not show the employee's ID or his or her name, if they do not work at any departments. That is because of the outer joined table being used in the WHERE clause. If there is no matching department, it will be null (not 999, even though 999 exists in employees).

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和姓名).部门列将为空，但我们得到一个结果(员工方面).

Now the criteria is in the on clause. So even if this employee works at no departments, he will still be returned (his ID and name). The department columns will be null, but we get a result (the employee side).

您可能会认为您永远都不想在WHERE子句中使用外部联接表，但不一定是这种情况.出于上述原因，通常是这样.

You might think you would never want to use the outer joined table in the WHERE clause, but that is not necessarily the case. Normally it is, for the reason described above, though.

假设您希望所有部门都没有员工.然后，您可以运行以下命令，该命令确实使用外部联接，并且在where子句中使用外部联接表:

Suppose you want all departments with no employees. Then you could run the following, which does use an outer join, and the outer joined table is used in the where clause:

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

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

^^ Shows departments with no employees.

以上可能是您想在WHERE子句中使用外部联接表而不是ON子句的唯一合理原因(我想这是您的问题；内部联接和外部联接之间的差异是完全不同的主题).

The above is likely the only legitimate reason you would want to use an outer joined table in the WHERE clause rather than the ON clause (which I think is what your question is; the difference between inner and outer joins is an entirely different topic).

一个好的观察方法是:使用外部联接允许空值.然后，为什么要使用外部联接并说字段不应该为null且应等于"XYZ"?如果值必须为"XYZ"(不为null)，那么为什么指示数据库允许返回null?这就像说一件事，然后再覆盖它.

A good way to look at is this: You use outer joins to allow nulls. Why would you then use an outer join and say that a field should not be null and should be equal to 'XYZ'? If a value has to be 'XYZ' (not null), then why instruct the database to allow nulls to come back? It's like saying one thing and then overriding it later.

这篇关于详细解释JOIN与LEFT JOIN和WHERE条件表现建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！