SQL IN working but NOT IN does not work

Question

I have this query that will return correctly the values that are part of the IN clause, but when I change it to NOT IN, it does not return anything.
Does anyone have any suggestions?

select distinct 
    CAST( w.work_area AS CHAR(4) ) || s.code_id as WATT 
from 
    sys_code s, 
    work_area_master w 
where 
    s.code_type = '590' 
    and (
        CAST( w.work_area AS CHAR(4)) || s.code_id
    )  
    in (
        select substr(misc_flags, 1,6) 
        from sys_code where code_type = 'STA'
    );

Hint: NULLs will make `IN` behave differently from what you expect. — wildplasser, Mar 13 '13 at 18:20
Either `NULL`s are messing with your results, or every row is in that select — Lamak, Mar 13 '13 at 18:21
The `FROM sys_code s, work_area_master w` looks uncorrelated. Mybe you want to JOIN them on some common keyfield? — wildplasser, Mar 13 '13 at 18:28

score 1 · Answer 1 · answered Mar 13 '13 at 18:26

1

The Exclusion Principle (ie, that A or ~A is a tautology) does NOT apply whenever A may be NULL. When nullable fields exist, three valued loigc applies and the Exclusion Principle must be modified to ( A or ~A or A is NULL).

answered Mar 13 '13 at 18:26

Pieter Geerkens

11,775
2
32
52

wildplasser · Answer 2 · 2013-03-13T19:34:28.880

1

SELECT DISTINCT
    CAST( w.work_area AS CHAR(4) ) || s.code_id AS what 
FROM sys_code s
JOIN work_area_master w ON 1=1 
WHERE s.code_type = '590' 
AND EXISTS (
    SELECT * FROM sys_code xx
    WHERE xx.code_type = 'STA'
    AND substr(xx.misc_flags, 1,6) = CAST( w.work_area AS CHAR(4)) || s.code_id
    );

Footnote: I deliberately used the JOIN ... ON 1=1 syntax to draw attention to the fact that the original query did not even have a join condition (except for the one in the correlated IN subquery)

edited Mar 13 '13 at 19:34

answered Mar 13 '13 at 19:14

wildplasser

43,142
8
66
109

1

If you mean to have a cross join, write one using the the CROSS JOIN syntax. Don't join on 1=1 – HLGEM Mar 13 '13 at 19:19
Why? This one stands out more (I still don't know if it is the OP's intention to do a carhesian product, except for the subquery) – wildplasser Mar 13 '13 at 19:24
`EXISTS` >> `IN`. And I took the liberty to make it a proper `CROSS JOIN` - I hope that's ok with you. – Erwin Brandstetter Mar 13 '13 at 19:24
I Hate the syntactic sugar. for me `1=1` reads easyer than all the fuzz words. – wildplasser Mar 13 '13 at 19:25
Sorry to have interfered then, roll back the change if you disagree. I do think it's better to user proper `CROSS JOIN`, though. – Erwin Brandstetter Mar 13 '13 at 19:27
Becasue 1=1 is meaningless and will confuse later developers. CROSS JOIN is clear. – HLGEM Mar 13 '13 at 19:28
So I did. I think I can afford a personal style. Besides, it is an entirely different issue. No, 1=1 is NOT meaningless. It draws the attention, more than a buzzy keyword. There are already too many keywords in SQL. – wildplasser Mar 13 '13 at 19:29
Well, I disagree, but it's a valid POV. +1 on the solution with `EXISTS`. – Erwin Brandstetter Mar 13 '13 at 19:44
The construct `IN ( *correlated* subquery)` is even worse, IMHO. It is valid syntax, but sloppy semantics. – wildplasser Mar 13 '13 at 19:46

score 0 · Answer 3 · edited May 23 '17 at 10:30

The check whether the combination of sys_code and work_area_master is valid can actually be the JOIN condition.

Also using EXISTS instead of IN to avoid problems with NULL like @wilplasser already provided:

SELECT DISTINCT
       CAST( w.work_area AS CHAR(4) ) || s.code_id AS what 
FROM   sys_code s
JOIN   work_area_master w ON EXISTS (
          SELECT 1
          FROM   sys_code x
          WHERE  x.code_type = 'STA'
          AND    substr(x.misc_flags, 1,6)
                 = CAST(w.work_area AS CHAR(4)) || s.code_id
          )
WHERE  s.code_type = '590' ;

For a detailed explanation of the problem with NOT IN and NULL, refer to this closely related question on dba.SE.

Octopus · Accepted Answer · 2013-03-13T18:27:39.860

-1

One could include a coalesce function like this:

select distinct CAST( w.work_area AS CHAR(4) ) || s.code_id as WATT
from sys_code s, work_area_master w
where s.code_type = '590' 
and (CAST( w.work_area AS CHAR(4)) || s.code_id) in (
select substr(coalesce(misc_flags,"    "), 1,6)
from sys_code
where code_type = 'STA'
);

edited Mar 13 '13 at 18:27

answered Mar 13 '13 at 18:24

Octopus

144
9

1

Do not encourage the use of a SQL antipattern of implicit joins. In this case it appears he has a cross join which may or may not be what he wants and the next person to maintain this won't know if it was a bug or intentional. This is especially dangerous if a later change adds and addtional code_type. – HLGEM Mar 13 '13 at 19:08
I agree with you HLGEM. However, some crossjoins can be fully implemented if this is the deliberate intention of the programmer. That's why I firmly believe one of the best practices is to comment on code, rather than letting someone else understand what was the author's intent on creating such a code. – Octopus Mar 13 '13 at 19:12
2

If you use explict joins you can only have a cross join if you write one. That is one of the reasons why the implict join is an extremely poor idea if you intend one as your intentinos are not clear. Even if you comment, comments are so often out of date that many devs won't realize it was still pertinent. If you use code, the intention is always clear. – HLGEM Mar 13 '13 at 19:17
Brilliant HLGEM! Thanks for pointing that. I'll look forward on my own code to avoid such a trap. – Octopus Mar 13 '13 at 19:29

SQL IN working but NOT IN does not work

4 Answers4