Simple MySQL query :- What's wrong in it?

Question

I am executing a MySQL query :-

SELECT * 
  FROM mytable 
 WHERE name LIKE '%D%' 
   AND id!='1'

It returns 1 result :-

name : Deepak
id: 2
username: NULL

It is working fine.

Problem is here.. :-

SELECT * 
  FROM mytable 
 WHERE name LIKE '%D%' 
   AND id!='1' 
   AND username!='d'

It returns 0 result , although username is null in mytable , but still it doesn't return the row of "Deepak" , as it returned in former query.

Please could you explain , why is it happening?

Because NULL is its own thing. It's neither equal nor not equal to anything else- not even NULL! — Strawberry, Jun 10 '17 at 18:32

score 1 · Accepted Answer · answered Jun 10 '17 at 18:33

1

As shown in this post, you need to check if the username "is null" or "<> 'd'".

SELECT * 
  FROM mytable 
 WHERE name LIKE '%D%' 
   AND id != '1' 
   AND (username IS NULL OR username != 'd')

answered Jun 10 '17 at 18:33

Blue

22,608
7
62
92

BenM · Answer 2 · 2017-06-10T18:43:25.677

0

You can't query a NULL since it represents no value. You should update your query to use IS NULL as follows:

SELECT * 
  FROM mytable 
 WHERE name LIKE '%D%' 
   AND id != 1 
   AND (username IS NULL OR username != 'd')

edited Jun 10 '17 at 18:43

answered Jun 10 '17 at 18:33

BenM

52,573
26
113
168

score 0 · Answer 3 · answered Jun 10 '17 at 18:34

null is not a value, it's the lack thereof. Think of it as an "unknown" value. Whenever you use it in an operator that expects a value (such as =), the result would be null (unknown). E.g. "What's the user's name? I don't know. Well, is it different than 'd'? Still don't know". If you want to handle nulls, you'd have to do so explicitly with the is operator:

SELECT * 
  FROM mytable 
 WHERE name LIKE '%D%' 
   AND id!='1' 
   AND (username IS NULL OR username!='d')

Simple MySQL query :- What's wrong in it?

3 Answers3