关于在leftjoin的on⼦句中限制左边表的取值时出现⾮期望的结
果多表left join
使⽤的SQL⼤概是这样的：
select * from A left join B on A.id=B.id and A.id>10; --错误的使⽤
我们期望的结果集应该是 A中的id>10，但是实际上A.id>10 这个限制条件并没有起作⽤。
应该改成如下的这种形式：
select * from A left join B on A.id=B.id where A.id>10;--正确的使⽤
这是在oracle的官⽅⽂档中到的相关说明：
left outer join
The result of a left outer join for table A and B contains all records of the left table A,
even if the join condition does not match a record in the right table B. For example, if
you perform a left outer join of employees (left) to departments (right), and if some
employees are not in a department, then the query returns rows from employees
with no matches in departments.
这是在《Database System Concepts》这本书中到的相关说明：
The right outer join is symmetric with the left outer join: It pads tuples
from the right relation that did not match any from the left relation with nulls and
adds them to the result of the natural join. In Figure 6.18, tuple (58583, null, null,
null, null, Califieri, History, 62000), is such a tuple. Thus, all information from the
right relation is present in the result of the right outer join.
⼤致的意思是，left join的结果集⼀定会包含左边表的所有记录。同理，right join⼀定会包含右边表的所有记录。
所以，使⽤时应该只在on⼦句中包含关联条件，单独对某个表的限制应该放到where⼦句中。
只是不知道，如果在left join的on⼦句中单独限制右边的表会不会有利于减少中间表的⼤⼩。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。