SQL left join issue with null values

Question

I inherited a query that uses left joins. One of the things the query is doing is removing any archived records, that is where archived = 'Y'. This is how the query looks:

    select P.firstname, E.entityid, C.committeeid, L.locationname
    from Pract P
    left join Committee C
    on P.commiteeid = C.committeeid
    and c.archived = 'N'
    left join Entity E
    on P.entityid = E.EntityID
    and e.archived = 'N'
    left join Location L
    on E.location = L.location
    and l.archived = 'N'

The result should only return records where archived <> 'Y'. I think a problem with putting the filter with the "on" is that it will return a record where c.archived = 'N' and just put a null in the archived field, which is not correct:

    FirstName    EntityID     CommitteeId
    John            55         null

If c.archived = 'Y' then the record should not show up.

I believe the archived filter should be in the where clause, like this:

    select firstname, entityid, committeeid
    from Pract P
    left join Committee C
    on P.commiteeid = C.committeeid
    left join Entity E
    on P.entityid = E.entityid
    left join Location L
    on E.Locationid = L.locationid
    where c.archived = 'N'
    and e.archived = 'N'
    and l.archived = 'N'

The problem I'm finding is that there are instances where the archived field from Committee is null(it's not a 'Y' or an 'N'). Using my solution incorrectly eliminates the records since null <> 'N.'

If I try this:

   where c.archived <> 'Y'

it does not work, I'm guessing because NULL does not evaluate to anything.

If I try this:

   where (c.archived = 'N' or c.archived is null)

it doesn't work as it now brings back those null records caused by the left join. I can't replace the left join with an inner join because that will exclude records where c.committeeid is null.

I just want to bring back records where archived <> 'Y', which includes those where the field is null.

To be clear, this is what the records in the table can look like:

    FirstName    EntityID   Archived
     John           55          Y
     Tom            56          NULL
     Rob            57          N

In this instance I want the returned records to look like:

    Tom             56          NULL
    Rob             57          N

John would be eliminated because Archived = 'Y.'

Is there another way to do this?

*"I think a problem with putting the filter with the "on" is that it will return a record where c.archived = 'N' and just put a null in the archived field, which is not correct."* It does exactly what it should. You are making a **left join** which means all element from **left** table should be included in the result. As no match is found on right table complying that filter, then you get null values for these columns. If you want only elements with matches in **right** table then do an **inner join**. — derloopkat, Jun 28 '17 at 19:29
I inherited this code and I'm trying to make it work correctly. I believe the original developer did not write this the proper way. I do not want the record returned if "archived = 'N' so the left join is not appropriate. But I do want the record returned if the "archived" field in the table is Null. I cannot do an inner join because there are instances where the field in the tables on the right side of the joins are null. An inner join will eliminate those. I cannot do "on P.commiteeid = C.committeeid" if C.committeeid is null. I need it to return only records where "archived <> 'Y'. — jackstraw22, Jun 28 '17 at 19:54
http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN — HLGEM, Jun 28 '17 at 19:55
Can you show us the example data and the expected results? I am not clear on what you are trying to achieve. — HLGEM, Jun 28 '17 at 19:59

score 0 · Answer 1 · answered Jun 28 '17 at 19:17

If I understand correctly, you want to use another field in the where clause for the filtering:

select firstname, entityid, committeeid  -- this will return an error on committeeid
from Pract P left join
     Committee C
     on P.commiteeid = C.committeeid and
        (c.archived <> 'Y' or c.archived is null)
where c.committeeid is not null;

You may also be able to do this with exists more easily:

select p.*
from Pract P
where not exists (select 1
                  from Committee C
                  where P.commiteeid = C.committeeid and
                        c.archived = 'Y'
                 );

This handles the NULL values automagically.

My query actually has about 15 left joins and pulls fields from each table. I updated it to give a more accurate representation with a few of those joins. — jackstraw22, Jun 28 '17 at 19:47

Marek Vitek · Answer 2 · 2017-06-28T21:34:11.960

0

I believe that you are looking for this version. You just start with what you had and modify joining condition to cover values that are not equal to Y and that are NULL.

select firstname, entityid, committeeid
from Pract P
left join Committee C
on P.commiteeid = C.committeeid
and (c.archived <> 'Y' OR c.archived IS NULL)

Then if you need only items that has record in commitee table. you put it in WHERE clause.

WHERE P.commiteeeid = C.commiteeid

But it will just emulate INNER JOIN.

Your problem with comparing NULL is that you can't use <> to test for NULL values. If you want records with NULL values you have to test for IS NULL explicitly.

So your updated query would look like:

select P.firstname, E.entityid, C.committeeid, L.locationname
from Pract P
left join Committee C
on P.commiteeid = C.committeeid
and (c.archived <> 'Y' OR c.archived IS NULL)
left join Entity E
on P.entityid = E.EntityID
and (e.archived <> 'Y' OR e.archived IS NULL)
left join Location L
on E.location = L.location
and (l.archived <> 'Y' OR l.archived IS NULL)

And if you have only values 'Y' 'N' and NULL, then you can also use

and (c.archived = 'N' OR c.archived IS NULL)

Just to be sure we understand each other here is minimal example for your query

create table Pract (firstname varchar(20), cid int)
create table comt ( cid int, archived varchar(1) )

insert into Pract values ('Tom',1),('Adam',2),('Mark',3),('Bob',4)
insert into comt values (1,'Y'), (2,'N'), (3,NULL)

--

select firstname, P.cid, C.cid, C.archived
from Pract P
LEFT join comt C
on P.cid = C.cid
and (c.archived <> 'Y' OR c.archived IS NULL)

With result

  firstname cid cid archived
1     Tom   1   NULL    NULL
2     Adam  2   2       N
3     Mark  3   3       NULL
4     Bob   4   NULL    NULL

Or with INNER JOIN

select firstname, P.cid, C.cid, C.archived
from Pract P
INNER join comt C
on P.cid = C.cid
and (c.archived <> 'Y' OR c.archived IS NULL)

With result

  firstname cid cid archived
1   Adam    2   2   N
2   Mark    3   3   NULL

edited Jun 28 '17 at 21:34

answered Jun 28 '17 at 19:54

Marek Vitek

1,573
9
20

I need the record to be eliminated only if the archived value = 'Y'. If the archived value in the Committee table is NULL then I need it to return with a null value. An INNER JOIN will remove the entire record in this instance. – jackstraw22 Jun 28 '17 at 20:04
OK I hope I finally got it right. You need all from pract and from comitte only when archived <> Y which includes NULL values. It is what this should return. – Marek Vitek Jun 28 '17 at 20:16
@Marek--Yes. I've updated the question again to show what needs to be returned. – jackstraw22 Jun 28 '17 at 20:18
It will also return `Tom NULL NULL` in case there is no match in Comittee table. Otherwise you need INNER jOIN. – Marek Vitek Jun 28 '17 at 20:21
@Marek--I need left joins because there are times when there is no match. It's fine if the fields are NULL in that instance. I just want to remove records where archived = 'Y.' – jackstraw22 Jun 28 '17 at 20:31
Then I think that the first part of the query without WHERE is for you. Just make sure that you are putting condition `and (c.archived <> 'Y' OR c.archived IS NULL)` in the ON and not in WHERE as it makes difference in your query. If you put it in the ON as I worte it, then you are filtering on table Committee, but in where you are filtering on result of join, where NULL is also for records without match. – Marek Vitek Jun 28 '17 at 20:36
But if you put it in the ON then won't it still return records where there is no match to Committee since it's a LEFT JOIN? I would think this would return a NULL Committee value, but not as a result of a NULL field in the Committee table. – jackstraw22 Jun 28 '17 at 21:02
It would return all records from Pract table and from Committee table joined data where archive <> 'Y' . If you want only records where is match in Committee, then simply replace LEFT JOIN to INNER JOIN. Or use additional WHERE in my response. And yes it would return NULL as a result of not matching committeeid but not as a result of archived being NULL. – Marek Vitek Jun 28 '17 at 21:12

domenicr · Answer 3 · 2017-06-28T20:06:59.600

-1

The solution depends on how you want to handle the NULL values from the left join. If NULL means not archived then

select firstname, entityid, committeeid
    from Pract P
    left join Committee C
    on P.commiteeid = C.committeeid
    left join Entity E
    on P.entityid = E.entityid
    left join Location L
    on E.Locationid = L.locationid
    where (c.archived = 'N' OR c.archived IS null)
    and (e.archived = 'N' OR e.archived IS null)
    and (l.archived = 'N' OR l.archived IS NULL)

edited Jun 28 '17 at 20:06

answered Jun 28 '17 at 19:53

domenicr

352
3
14

This changes the left join to an inner join and is not ever recommended. – HLGEM Jun 28 '17 at 19:54
@HLGEM what are you saying? How is this changing to inner join – domenicr Jun 28 '17 at 19:56
I tried this in the example I gave. This method eliminates records where the archived field is NULL. – jackstraw22 Jun 28 '17 at 20:07
@jackstraw22 try it now – domenicr Jun 28 '17 at 20:08
I originally tried the (c.archived = 'N' or c.archived is null) and it brought back the null records from the left join. – jackstraw22 Jun 28 '17 at 20:13

SQL left join issue with null values

3 Answers3