左外部联接未从主表返回所有记录 [英] Left Outer Join not returning all records from primary table
问题描述
当我执行左外部联接时,我希望在添加联接表之前获得查询将返回的所有记录,但是它仅返回与联接表匹配的记录(即:'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.
这篇关于左外部联接未从主表返回所有记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!