24
table 'materials'
id   sku  content
10   IT2   Iron
11   IT3   Steel
12   IT4   Steel
13   IT5   NULL
14   IT6   Iron
15   IT7   Glass

select id, sku, content from materials where content !='Iron';

Returns Result:

id   sku   content
11   IT3   Steel
12   IT4   Steel
15   IT7   Glass

Why is id #13 with the NULL value not returned in the result set? Using MYSQL.

Kevin
  • 349
  • 1
  • 2
  • 10
  • 4
    You could.... `where coalesce(content,'HandleNull') !='Iron'` coalesce will take the first non-null value however 'HandleNull' in this case becomes a special edge case; and if any content values ever equal 'HandleNull' then you woudln't get those. – xQbert Sep 04 '15 at 20:02
  • xQbert - This also works perfectly. I see that handleNull is a placeholder that can be anything that you know will not be a possible result. Is it turning all the NULLs into 0? – Kevin Sep 04 '15 at 21:09
  • coalesce returns the first non-null value from a series of values. So in this case, if content is null it will return 'HandleNull' which will likely not equal your possible results. However, if content has a value it will simply compare that value to != 'Iron' it's not really turning them to 0. I couldn't find how mySQL handled empty set '', it may work just as well. – xQbert Sep 06 '15 at 14:22
  • @xQbert Thanks for that workaround, it helped a lot for my situation where the where clause was being built dynamically and it would have been extremely difficult to add the 'xyz is null' wherever it was needed. I know this "feature" of SQL is by design, but personally feel that it is a stupid design decision and that 99% of the time people want NULL != 'Iron' to return TRUE; so it should have been designed to return what people would expect it to return. – BruceHill Mar 15 '18 at 09:39
  • Does this answer your question? [Why does NULL = NULL evaluate to false in SQL server](https://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server) – philipxy Mar 03 '23 at 02:13

2 Answers2

37

As per your where clause it compares null != 'Iron', which evaluates to UNKNOWN which neither true nor false based on SQL's 3 way logic. Hence it is not returned by the query.

If you need the null row to be resulted, you should use

where content !='Iron' or content is null

Edit: One more option is to use the relational null-safe equality operator <=> with a negation.

not content <=> 'Iron'

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

The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

mysql> 
   SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL
   UNION
   SELECT 1 = 1, NULL = NULL, 1 = NULL;

Returns

  1 vs 1  |  NULL vs NULL  | 1 vs NULL |  Comment
  --------------------------------------------------
     1    |        1       |    0      |  for <=>
     1    |       NULL     |   NULL    |  for  =
surfmuggle
  • 5,527
  • 7
  • 48
  • 77
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • 1
    in PG `ERROR: operator does not exist: integer <=> integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts.` – Al-Mothafar May 06 '19 at 03:09
  • @Al-Mothafar I hope you see question is tagged with mysql? – Coderino Javarino Mar 17 '21 at 11:41
  • @CoderinoJavarino most SQL DBMS share the same syntax, operators usually the same, so I just commented with my experience in PG, not only question tagged with MySQL, but even the answers it is clear `mysql> ` – Al-Mothafar Mar 17 '21 at 12:08
1
ISNULL(content, '') = 'Iron'

This will convert the null to an empty string.

Arghya Sadhu
  • 41,002
  • 9
  • 78
  • 107
Shanuke Dissanayake
  • 11
  • 1