左外部联接未从主表返回所有记录 [英] 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屋！