2

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

Improve this question
4
  • I tried same thing and it does return zero rows. Why? Commented Jun 13, 2010 at 20:52
  • even Select * from titles_and_tags` WHERE tag IS NULL` will return zero i think Commented Jun 13, 2010 at 20:59
  • @Taz: Not for me. Are you sure that you are executing the query correctly? Does SELECT * FROM titles_and_tags return any rows? Commented Jun 13, 2010 at 21:00
  • I must be something wrong then. SELECT * FROM titles_and_tags returns all rows. Commented Jun 13, 2010 at 21:02

3 Answers 3

Reset to default
5

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.

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

5 Comments

OK, the wildcard isn't the problem, it exists, i just forgot to add it here, but thanks for perception :)
+1 Although the parentheses do matter for this query. The example data just doesn't demonstrate a difference. Try inserting a row (4, 'nottitle', NULL) and then compare with and without parentheses.
Ok, now this represents my current database structure, nearly perfect (except, there are a few more columns :))
Then you're leaving something else that's important out of the question, for the table and query given this should work.
This is the correct answer. I've replicated your case and it works, the parenthesis are the most important. Without them, you would get all columns where (title LIKE "title%" AND tag = "tag1") OR tag IS NULL.
0

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.

Improve this answer

Comments

0

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)
Improve this answer

1 Comment

you only need backticks for MySQL keywords, and referencing column aliases in the GROUP BY/HAVING clauses.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.