Tuesday 7 May 2019

Be careful when doing comparisons on an attribute whose value may be NULL in SQL

I write this quick note to remind myself to be careful when working with comparison operators on an attribute that may take NULL value.
For example we have a table USER storing the survey users with the attribute u_issubmitted that takes the value 1 when the user has submitted the survey, and NULL when the user has not submitted the survey.
We assume that there are 59 users in the table USER, 51 of them have submitted the survey and 8 of them have not submitted the survey. Let's try to run some SQL statements on the table USER.
The following SQL statement
select count(*) from users;
returns 59 as we expected.
The following SQL statement
select count(*) from users where u_issubmitted = 1;
returns 51 as we expected.
However, the following both two SQL statements do not return value 8 as we expected, instead, value 0 is returned. These statements look correct logically but they are technically incorrect.
select count(*) from users where u_issubmitted <> 1;
select count(*) from users where u_issubmitted != 1;
The correct SQL statement to count the number of users who have not submitted the survey is as following
select count(*) from users where u_issubmitted is NULL;
The above statement returns value 8 as we expected.
In conclusion, we should be careful when working on the attributes that may take value NULL. You can read more about how MySQL deals with NULL values in comparisons here.

1 comment:

  1. Once the banker has put up a stake, the opposite gamers place their bets. If any participant needs to bet the entire worth of the financial institution towards the banker, they call 'banco'. If a couple of participant calls 'banco', the one whose flip was earlier in counter-clockwise rotation from the banker has priority. If no one calls 'banco', then each participant in flip, starting to the banker's right and continuing counter-clockwise, can bet any amount offered that the whole bets are not greater than what is in the casino.edu.kg financial institution. If a participant's bet makes the whole equal to the financial institution then no additional bets may be placed, and any gamers whose turns are later have no stake in the coup.

    ReplyDelete