mysql query not showing null value [duplicate]

Question

i am have more than 100000 data in MySQL i have gender column some have female,male and null .i am writing query for showing all he data unfortunately it not showing the data which has null it only showing data with male and female below is my query can anyone tell where i went wrong thanks

SELECT DISTINCT contact.`id` , contact.`contactgroup` , contact.`media` , contact.`media2` , contact.`email1` , contact.`nationality` , contact.`country3` , contact.`twon` , contact.`area` , contact.`gender` , contact.`married` , contact.`children` , contact.`driverslicense`
FROM contact
WHERE isdeleted =0
AND (
`gender` = 'female'
OR `gender` = 'male'
OR `gender` = ''
OR `gender` = 'NULL'
)
LIMIT 180 , 30

echo_Me · Accepted Answer · 2014-07-03 08:38:10Z

2

change that

 OR `gender` = 'NULL'

to

OR `gender` is NULL

'NULL' is a string. AND NULL is NULL . have no length . (Null) .

answered Jul 3, 2014 at 8:38

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

prava · Accepted Answer · 2014-07-03 08:38:26Z

0

Try with this:

SELECT DISTINCT contact.`id` , contact.`contactgroup` , contact.`media` , 
contact.`media2` , contact.`email1` , contact.`nationality` , contact.`country3` , 
contact.`twon` , contact.`area` , contact.`gender` , contact.`married` , 
contact.`children` , contact.`driverslicense`
FROM contact
WHERE isdeleted =0
AND (
    `gender` = 'female'
    OR `gender` = 'male'
    OR `gender` = ''
    OR `gender` IS NULL
)
LIMIT 180 , 30

answered Jul 3, 2014 at 8:38

prava

3,9762 gold badges26 silver badges35 bronze badges

Comments

Samar Haider · Accepted Answer · 2014-07-03 08:40:47Z

0

change that

 OR `gender` = 'NULL'

to

OR isnull(`gender`)

answered Jul 3, 2014 at 8:40

Samar Haider

9121 gold badge10 silver badges21 bronze badges

Comments

Dinesh G · Accepted Answer · 2014-07-03 08:45:44Z

0

Here you have given

gender = 'female' OR gender = 'male' OR gender = '' OR gender = 'NULL'

Then why should you use as a condition

SELECT contact.id , contact.contactgroup , contact.media , contact.media2 , contact.email1 , contact.nationality , contact.country3 , contact.twon , contact.area , contact.gender , contact.married , contact.children , contact.driverslicense
FROM contact WHERE isdeleted =0  LIMIT 180 , 30

answered Jul 3, 2014 at 8:45

Dinesh G

2421 silver badge14 bronze badges

Comments

Sadikhasan · Accepted Answer · 2014-07-03 08:57:25Z

0

Change from

OR `gender` = 'NULL'

to

OR `gender` IS NULL

Check Manual for IS NULL.

edited Jul 3, 2014 at 8:57

answered Jul 3, 2014 at 8:46

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

Collectives™ on Stack Overflow

mysql query not showing null value [duplicate]

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Related