常量值如何影响Joins的ON子句? [英] How do constant values effect the ON clause of Joins?

问题描述

我最近发现LEFT JOIN的ON子句可能包含(1 = 1)之类的值.

I've recently discovered that the ON clause of a LEFT JOIN may contain values such as (1 = 1).

这让我很沮丧，因为它破坏了我对联接功能的理解.

This is upsetting to me, as it breaks my perception of how joins function.

我遇到了以下情况的更详细的版本:

I've encountered a more elaborate version of the following situation:

SELECT DISTINCT Person.ID, ...
FROM Person LEFT JOIN Manager 
ON (Manager.ID = Person.ID OR Manager.ID = -1))
WHERE (...)

这是完全合法的.如果有的话，"Manager.ID = -1"会完成什么工作?如何影响加入?

It's perfectly legal. What does "Manager.ID = -1" accomplish, if anything? How can this effect the Join?

推荐答案

如果人员表为:

id  name

1   Person One
2   Person Two
3   Person Three
4   Person Four
5   Person Five

如果经理表是

id  name
-1  Admin
2   Manager One
3   Manager Two

如果查询是:

SELECT DISTINCT *
FROM Person LEFT JOIN Manager 
ON (Manager.id = Person.id OR Manager.id = -1)

那么结果是:

Person One  -1  Admin
Person Two  -1  Admin
Person Two  2   Manager One
Person Three    -1  Admin
Person Three    3   Manager Two
Person Four -1  Admin
Person Five -1  Admin

这里所有人员行都以-1 Admin(在manager表上)联接，并且如果manager表中存在相同的ID，则会再发生一次联接.

Here all person rows joins with the -1 Admin (on manager table) AND if the same id exist in manager table one more join occurs.

这篇关于常量值如何影响Joins的ON子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！