FishBulbX FishBulbX - 6 months ago 7
SQL Question

Left Outer Join not returning all records from primary table

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)


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

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).

To avoid this issue use

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

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