NOT Combination with AND

Question

I am trying to query a list of rows from the database. It has 7 fields (columns) (A,B,C,VA,VB,VC,LISTED).

SELECT * FROM datas WHERE 
        ((A=1 OR A IS NULL) AND (B=1 OR B IS NULL) AND (C=1 OR C IS NULL))  -- A/B/C are either 1 or NULL
        AND (VA=1 AND VB=1 AND VC=1)    -- VA/VB/VC must ALL be 1
        AND LISTED=0
        AND NOT (A=1 AND B=1 AND C=1)   -- BUT A/B/C must NOT be ALL 1 (at least one NULL)
ORDER BY ID ASC

Everything is working until i add the AND NOT line. It returns no row.

To explain it shorter, i want to retrieve all rows where A/B/C are either 1 or NULL but not ALL to 1.

And VA/VB/VC must be all to 1. (and LISTED is well 0)

Thanks :)

Answer 1

Everything is working until i add the AND NOT line. It returns no row.

The problem is comparisons against null values in the NOT predicate. A = 1 returns null when A is null, then NOT (NULL) is still NULL. Obviously this is not the behavior you want.

Instead, you can use <=>, which performs null-safe equality:

SELECT *
FROM data
WHERE 
    COALESCE(A, 1) = 1 AND COALESCE(B, 1) = 1 AND COALESCE(C, 1) = 1 -- A/B/C are either 1 or NULL
    AND VA = 1 AND VB = 1 AND VC = 1 -- VA/VB/VC must ALL be 1
    AND LISTED=0
    AND NOT (A <=> 1 AND B <=> 1 AND C <=> 1)   -- BUT A/B/C must NOT be ALL 1 (at least one NULL)
ORDER BY ID ASC

Notes:

• COALESCE() can be used to shorten the first predicate
• Parentheses around AND conditions are superfluous (second predicate)

Answer 2

in mysql comparisons return 0 or 1 so they can be summed

SELECT * FROM datas WHERE 
        ((A=1 OR A IS NULL) AND (B=1 OR B IS NULL) AND (C=1 OR C IS NULL))  -- A/B/C are either 1 or NULL
        AND (VA=1 AND VB=1 AND VC=1)    -- VA/VB/VC must ALL be 1
        AND LISTED=0
        AND ((A IS NULL) +( B IS NULL) + ( C IS NULL)) Between 1 and 2

-- BUT A/B/C must NOT be ALL 1 (at least one NULL) ORDER BY ID ASC