左外部联接未从主表返回所有记录

本文介绍了左外部联接未从主表返回所有记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当我执行左外部联接时，我希望在添加联接表之前获得查询将返回的所有记录，但是它仅返回与联接表匹配的记录(即:'092387'的无记录)表'documentation'中存在该文件，因此我只想为该记录的'文件名'字段返回null.)我在做什么错了?

When I do a left outer join, I expect to get all the records that the query would return prior to adding the joined table, but it is only returning records that match the joined table (i.e: no record for '092387' exists in table 'documentation', so I just want null returned for 'filename' field for that record.) What am I doing wrong?

mysql> select documentation_reference.ref_docnumber , documentation.filename from documentation_reference left outer join documentation on ref_docnumber=documentation.docnumber where documentation_reference.docnumber='TP-036' and documentation.status!=3; +---------------+-----------------+ | ref_docnumber | filename | +---------------+-----------------+ | SOP-0042 | SOP-0042r39.pdf | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> select ref_docnumber from documentation_reference where documentation_reference.docnumber='TP-036'; +----------------------+ | ref_docnumber | +----------------------+ | 092387 | | 1100218B | | Applicable Item Spec | | SOP-0042 | +----------------------+ 4 rows in set (0.00 sec)

推荐答案

您的where子句将外部联接转换为内部联接.

Your where clause is converting the outer join back into an inner one.

outer join保留的不匹配行都将具有documentation.status的NULL值，因此您的documentation.status != 3条件将其过滤掉(表达式NULL !=3的结果为unknown而不true).

The non matching rows preserved by the outer join will all have NULL values for documentation.status so your documentation.status != 3 condition will filter these back out (The result of the expression NULL !=3 is unknown not true).

为避免此问题，请使用

select documentation_reference.ref_docnumber, documentation.filename from documentation_reference left outer join documentation on ref_docnumber = documentation.docnumber and documentation.status != 3 where documentation_reference.docnumber = 'TP-036'

请注意，documentation.status != 3谓词已移至JOIN条件.

Note that the documentation.status != 3 predicate is moved into the JOIN condition.