Sql query not returnig null values

Question

I have 2 tables:

req_docs

and pers_docs

My sql query:

SELECT *
FROM req_docs
LEFT OUTER JOIN pers_docs ON req_docs.doc_nid = pers_docs.doc_nid
WHERE req_docs.pos_id ="CPT"
  AND pers_docs.pers_nid = 6
  AND pers_docs.expires <= "2009-09-01"`

It returns only 2 rows. Which is ok, but I need also return not existing matches from pers_docs join as NULL values. Help please!

jarlh · Accepted Answer · 2016-05-22 17:00:02Z

2

When LEFT JOIN, put the right side table's conditions in the ON clause to get true left join behavior! (When in WHERE, you get regular INNER JOIN result.)

SELECT *
FROM req_docs
LEFT OUTER JOIN pers_docs ON req_docs.doc_nid = pers_docs.doc_nid
                          AND pers_docs.pers_nid = 6
                          AND pers_docs.expires <= "2009-09-01"
WHERE req_docs.pos_id = "CPT"

edited May 22, 2016 at 17:00

answered May 22, 2016 at 16:36

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql query not returnig null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related