MySql select on fields containing null values

16,824

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...

Share:
16,824
Marco
Author by

Marco

Updated on June 04, 2022

Comments

  • Marco
    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
    Marco almost 11 years
    Yes 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
    Marco almost 11 years
    Ok 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
    SQL.injection almost 11 years
    FlyBy i am not a server configuration expert. BTW try use '<>' instead of '!=', the SQL operator for difference is '<>'.
  • Marco
    Marco almost 11 years
    Nice hint to use <>! You are right! :) While writing this my brain was more php than mysql... Thanks! ^^
  • Isma
    Isma about 4 years
    I 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
    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.