CODEWITHSUNDEEP
mysql
therufa
therufa

Reputation: 2041

MySQL: fetching a null or an empty string

I know whats the difference between a NULL value and an empty string ("") value, but if I want to get a value by using the OR keyword, I get no result for a NULL value

The table i want to query looks like this:

 titles_and_tags
+----+----------+------+
| id | title    | tag  |
+----+----------+------+
|  1 | title1   | NULL |
|  2 | title2   | tag1 |
|  3 | title3   | tag2 |
|  4 | edit     | NULL |
|  5 | rowdata  | div  |
+----+----------+------+

The query i use looks like this:

select * 
  from `titles_and_tags` 
 WHERE `title` LIKE "title%" 
   AND `tag` = "tag1" OR `tag` IS NULL

So i want to get here a rows (id: 1,2), BUT this results 0 rows. What have i done wrong?

EDIT

Sorry, i forgot thats my main problem is this:

select * 
  from `titles_and_tags` 
WHERE `title` LIKE "title%" 
AND `tag` = "tag1" OR `tag` LIKE '%'

So this more like an off-topic, sorry

Upvotes: 2

Views: 4474

Answers (3)

Donnie
Donnie

Reputation: 46913

Try

select 
  * 
from 
  `titles_and_tags`
WHERE 
  `title` LIKE "title%" AND 
  (`tag` = "tag1" OR `tag` IS NULL)

You left the wildcard % off your like, plus you should enclose in parenthesis the or clause for clarity and to explicitly group the logic together to make sure it is executed in the way that you intended.

EDIT: In your edit tag like '%' will match all rows in which tag is not null. Any comparison other than is or not is with a null value is false. I'm not sure what you're trying to do with the last query, but I suspect the one that you asked the question with originally is more like what you actually want.

Upvotes: 5

Tasawer Khan
Tasawer Khan

Reputation: 6148

Try (Dont wrap the column name tag in anything. or if you wrap them use backtics instead of single quotes. as suggested by anthony)

select * 
  from `titles_and_tags` 
 WHERE title LIKE "title%" 
   AND (tag = "tag1" OR tag IS NULL)

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838146

Based on your comments my guess is that you have inserted either the string 'NULL' or the empty string '' into the database instead of the special value NULL. To confirm this try the following query:

SELECT * 
FROM titles_and_tags
WHERE tag IN ('NULL', '')

If this returns any rows, that's your problem.

Upvotes: 0

Related Questions