Alex Rivera | Logout

Checking for an empty field with MySQL

Asked 2010-02-24T15:13:52.717
109

I've wrote a query to check for users with certain criteria, one being they have an email address.

Our site will allow a user to have or not have an email address.

$aUsers=$this->readToArray('
 SELECT `userID` 
 FROM `users` 
 WHERE `userID` 
 IN(SELECT `userID`
         FROM `users_indvSettings`
  WHERE `indvSettingID`=5 AND `optionID`='.$time.')
  AND `email`!=""
 ');

Is this the best way to check for an empty field in SQL? I've just tried "IS NOT NULL" and this still returned a users record without them having an email address.

The query above works but out of curiosity I wondered if I'm doing it the correct way.

Edit
Report

2 Answers

287

An empty field can be either an empty string or a NULL.

To handle both, use:

email > ''

which can benefit from the range access if you have lots of empty email record (both types) in your table.

answered 2010-02-24T15:20:08.647
14

You could use

IFNULL(email, '') > ''
answered 2013-07-08T12:32:43.693

Your Answer