MySql select on fields containing null values
Because null is a special case, if you want null values to be included, you should explicitly specify that you want them.
SELECT * FROM table
WHERE (data1
<> 'abc' or data1
is null)
The expected server behavior, is ignore nulls unless you ask for them...
Marco
Updated on June 04, 2022Comments
-
Marco almost 2 years
In our company we moved our web application (LAMP) from one server (Ubuntu 10.04) to a new server (Ubuntu 12.04.2). Now we encountered a strange behavior I haven't seen before and I really do not know where to begin. Maybe someone can give me hint.
We have got the following simple table:
id data1 data2 data3 (int) (varchar) (int) (int) ------------------------------------- 1 (empty) 123 456 2 (null) 321 654 3 abc 555 666
(empty) means the field contains a empty string. (null) means that the field is null. Now we use the following very very simple query:
SELECT * FROM `table` WHERE `data1` != 'abc';
On our old server the query returned the lines with the ids 1 and 2 which, I guess, is absolutely correct since
!='abc'
matches those two recordsets.On our new server the query only returns the recordset with the id 1. Recordsets containing null in the select fields are suddenly ignored by the query somehow.
Just to make it more clear: I know that
IS NULL
could be used, but that would result in checking all queries and tables in the application matching this situation.Now the questions are:
Did we had luck on our old server that the query behaved as expected by returning lines 1 and 2 or does the new server behave correct by returning only line 1?
Generally: Should
!='abc'
match the recordsets 1 and 2 or should it only match id 1?Is it possible that there is a setting in the mysql configuration that controlls that behaviour? I am a little stuck with that. Every help is appreciated!
Thanks in advance...
-
Marco almost 11 yearsYes sure I know that I can solve this by using
is null
, but that means we have to go through our complete application and check all queries and tables. I guess that this behaviour is a other problem that can be solved in a other way. I am still pretty sure that!='abc'
should match the lines 1 AND 2... -
Marco almost 11 yearsOk I see... Now the question is why was it possible on our old server to get the requested lines 1 and 2? I do not think that up-to-date mysql servers on Ubuntu 10.04 are that kind of old that they did not follow this rules of handlings null values?
-
SQL.injection almost 11 yearsFlyBy i am not a server configuration expert. BTW try use '<>' instead of '!=', the SQL operator for difference is '<>'.
-
Marco almost 11 yearsNice hint to use
<>
! You are right! :) While writing this my brain was more php than mysql... Thanks! ^^ -
Isma about 4 yearsI have had a field type integer where only two records have had value 1, others were null. When I ask Select * from Table where field1=1 I got right result but when I typed: SELECT * from Table where field <>1 I didn't get anything! Weird.
-
SQL.injection almost 4 years@Isma the expected behaviour is for null values to be excluded from the query. In the original answer i provided reference to the mysql documentation.