当我执行左外部联接时,我希望在添加联接表之前获得查询将返回的所有记录,但是它仅返回与联接表匹配的记录(即:'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.
更多推荐
左外部联接未从主表返回所有记录
发布评论