MySQL: fetching a null or an empty string

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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)