Mysql "like" and "not like"

[bbcanary]

Hi

I have a problem in my search, I search in a table for "like ..." and "not like ..." and I still end up with less than all the rows...

Here it is:

SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31"

result: 264 hits

SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and finished like "yes"

result: 212 hits

The final search should result in (264-212) hits (52):

SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and finished not like "yes"

But it only results in 38 hits...

I have tried exchanging like and not like with == and != ... Same result

Is this a problem with mysql, the search or what?

[sylow]

hello,

I just want to warn you about your like syntax

if you use
------
finished like "yes"
------

it is the same as
-------
finsihed = 'yes'
-------

Are you sure you wanna do this in more expensive way by using like?
or you wanna do
----------
finished like "%yes%"
---------

Correct that part and try again. Lets see what you will get.

IMHO

[bbcanary]

Hi

Sorry, didn't help... Same result.

(and usually I use %, but I didn't think it was necessary here...)

Extra information:

In mysql finished is enum ('yes','no','y','n') default NULL

and the missing rows all have '' (nothing) in finished, but there are some rows that have '' that I can find.

[CrazyCrane]

LIKE and NOT LIKE can't retrieve NULL values, because a NULL record is neither LIKE nor NOT LIKE anything - it's just NULL.

NULLs are a bit strange to get your head around, but it does make sense once you understand them. NULL is not the same as zero or blank; NULL means "unknown or unquantifiable value". NULL isn't equal to or like anything; in fact, NULL is not even equal to NULL!

With your query, when the database gets to a record with a NULL value in the finished field, it can't say that NULL is like 'yes' (because it doesn't know what NULL is exactly; it's an unknown value), and in the next query it can't say that NULL is not like 'yes' (again because it doesn't know what NULL is exactly; it's an unknown value).

The only way to test for NULLS is by using "IS NULL" or "IS NOT NULL". If you ran the query:

SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and finished IS NULL

you'd probably find your missing 14 records. If you wanted to combine them in the "not like" query, you could write it as:

SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and (finished NOT LIKE 'yes' OR finished IS NULL)

which should get you your 52 records.

(Note that you say there are "some records with '' that you can find" - I imagine these will have blank entries rather than NULL values, which means the NOT LIKE expression will pick them up as it can compare blank to 'yes').

Hope that helps, I don't know if I explained NULLs very well. If you need to read more to understand it, try www.mysql.com - there ought to be a decent explanation of NULLs there.

Hope that helps .

[bbcanary]

You explained it perfectly

And I found the missing 14!

Thanks to everybody for their help.