Checking for an empty field with MySQL

Question

I've written 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 user's 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.

Answer 1

To handle both of them, try this:

email > ''

The above code will benefit you from the range access if you have lots of empty email records (both types) in your table.

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