INNER JOIN clause ignoring NULL values

Question

I am looking to query some data that pertains to medications a patient has been prescribed that are in a certain category. But I also want to show patients that do not have any medications. My query so far:

    SELECT
    pd.fname,
    pd.lname,
    pp.drug_name,
    pp.drug_strength
FROM
    patient_data pd 
     FULL OUTER JOIN patient_prescr pp on pp.pid = pd.pid 
     FULL OUTER JOIN formulary f on pp.med_id = f.id 
     INNER JOIN formulary_categories fc on f.category = fc.id AND fc.id in (34,36,37,38,5)
WHERE
    pd.lname = 'Test'

When applying the INNER JOIN to formulary_categories, I can correctly specify the category in which the drug I want to specify, but when I do this, it WILL NOT include patients that do not have any medications.

With the INNER JOIN joining the formulary_categories table, my results look like this:

-----------------------------------------------------------------------
fname       | lname          | drug_name                | drug_strength
-----------------------------------------------------------------------
Cathy         Test             Clonazepam                 0.5mg
Larry         Test             Librium                    25mg
Jennifer      Test             Vistrail                   25mg
-----------------------------------------------------------------------

If I change the INNER JOIN to a FULL OUTER JOIN, it simply ignores the category constraint, and pulls all categories.

However, the query will not include patients that do not have any medications prescribed. Id like my results to look something like:

-----------------------------------------------------------------------
fname       | lname          | drug_name                | drug_strength
-----------------------------------------------------------------------
Cathy         Test             Clonazepam                 0.5mg
Larry         Test             Librium                    25mg
Joe           Test             NULL                       NULL
Jennifer      Test             Vistrail                   25mg
Steve         Test             NULL                       NULL
-----------------------------------------------------------------------

Try `LEFT JOIN` instead of `INNER JOIN`. – Radu Gheorghiu Jun 08 '15 at 15:05 — Radu Gheorghiu, Jun 08 '15 at 15:05
`LEFT JOIN` instead of `INNER JOIN`? – dario Jun 08 '15 at 15:05 — dario, Jun 08 '15 at 15:05

score 8 · Accepted Answer · answered Jun 08 '15 at 15:06

8

You are actually looking for LEFT JOIN:

SELECT
    pd.fname,
    pd.lname,
    pp.drug_name,
    pp.drug_strength
FROM
    patient_data pd 
     FULL OUTER JOIN patient_prescr pp on pp.pid = pd.pid 
     FULL OUTER JOIN formulary f on pp.med_id = f.id 
     LEFT JOIN formulary_categories fc on f.category = fc.id 
         AND fc.id in (34,36,37,38,5)
WHERE
    pd.lname = 'Test'

A LEFT JOIN will not filter data if a correlation is not found between the values in the two tables (or result sets) and will display a NULL value for the columns which displays data from the table where a correlation was not found (just like in your expected output sample).

You can also take a look at the best article (in my opinion) for understanding all types of JOINs, here.

answered Jun 08 '15 at 15:06

Radu Gheorghiu

20,049
16
72
107

Thanks for the answer Radu. How do you handle `categories` that are `NULL`? I tried to apply: `LEFT JOIN formulary_categories fc on f.category = fc.id AND fc.id in (34,36,37,38,5) and fc.id IS NOT NULL`, but the results still contain drugs with NULL values. – etm124 Jun 08 '15 at 15:37
@etm124 If you want to filter the NULL values in this scenario you need to add your `fc.id IS NOT NULL` condition at the end, in the `WHERE` clause, like: `WHERE pd.lname = 'Test' AND fc.id IS NOT NULL`. Doing the `IS NOT NULL` check in the JOIN section you're directly looking in the table `fc.id` column if it is `NULL`, not in the displayed results. But I assume you have no `NULL` values in your `ID` column, hence no values are being filtered. Maybe you could also take a look at [**how a query is executed**](http://stackoverflow.com/a/2617679/1266737) and practice with LEFT and INNER joins – Radu Gheorghiu Jun 08 '15 at 16:08

score 1 · Answer 2 · answered Jun 08 '15 at 15:06

You simply need LEFT OUTER JOIN :

  SELECT
    pd.fname,
    pd.lname,
    pp.drug_name,
    pp.drug_strength
FROM
    patient_data pd 
     FULL OUTER JOIN patient_prescr pp on pp.pid = pd.pid 
     FULL OUTER JOIN formulary f on pp.med_id = f.id 
     LEFT OUTER JOIN formulary_categories fc on f.category = fc.id AND fc.id in (34,36,37,38,5)
WHERE
    pd.lname = 'Test'

INNER JOIN clause ignoring NULL values

2 Answers2