CODEWITHSUNDEEP
phpmysql
Saeed sdns
Saeed sdns

Reputation: 970

MySQL Comparison with NULL in Where condition

I have a MySql table structure like this: (for the English Sentence Learning System):

learn_status column can be 0 or 1.

3 practices columns can be NULL or -1 or 0 or 1.

╔═════════════╦══════════════╦════════════╦════════════╦════════════╦════════╗
║ sentence_id ║ learn_status ║ practice_1 ║ practice_2 ║ practice_3 ║ others ║
╠═════════════╬══════════════╬════════════╬════════════╬════════════╬════════╣
║      0      ║       1      ║    NULL    ║    NULL    ║    NULL    ║  more  ║
║      1      ║       0      ║      1     ║      0     ║      1     ║  more  ║
║      2      ║       0      ║     -1     ║    NULL    ║      0     ║  more  ║
║      3      ║       0      ║    NULL    ║    NULL    ║    NULL    ║  more  ║
║      4      ║       0      ║      1     ║      1     ║      1     ║  more  ║
╚═════════════╩══════════════╩════════════╩════════════╩════════════╩════════╝

I want to get sentences with 2 conditions:

  1. learn_status should be 0
  2. all 3 practices columns are not equal to 1 simultaneous.

(if practice_1=1 & practice_2=1 & practice_3=1 simultaneous, then it should not return record)

(if one of them are equal to 1, that is ok and then it should return record)

I use below MySQL code but it does not work correctly:

SELECT * FROM learnbox WHERE learn_status=0 AND NOT (practice_1=1 AND practice_2=1 AND practice_3=1)

It should return 3 records of the above table: sentence_id=(1 & 2 & 3) records.

but it return the only sentence_id=(1 & 2) and doesn't work correctly.

Thanks

Upvotes: 0

Views: 70

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Comparison operator = will return NULL if at-least one of the operand is NULL. So for the sentence_id = 3, you are getting NULL for all the three practice field conditions.

Also, NOT NULL is NULL; thus for sentence_id = 3, your Where condition will be TRUE AND NULL, which is equal to NULL, hence the row does not appear. Check more details about operators and their behavior at MySQL Documentation.

Solution 1: You can use Ifnull() function, to check for NULL returning from the practice field conditions, and set it to FALSE.

Try the following:

SELECT * FROM learnbox 
WHERE learn_status=0 
  AND NOT IFNULL((practice_1 = 1 AND 
                  practice_2 = 1 AND 
                  practice_3 = 1)
                 , FALSE)

Solution 2: You can also use null-safe equal operation (<=>). From documentation:

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

So it will return 0 here for the practice field conditions.

You may try the following as well:

SELECT * FROM learnbox 
WHERE learn_status = 0 
  AND NOT (practice_1 <=> 1 AND 
           practice_2 <=> 1 AND 
           practice_3 <=> 1)

Upvotes: 2

Related Questions