Difference between in and like statement in mysql

[prem2]

Hi team,

Kindly, clarify the below query, i am confused the difference between in and like statements in the mysql.

mysql>  select count(id) as tablename from postings where is_active=1 and publish in (2) ;
result -- 38 

mysql>  SELECT COUNT(*) FROM tablename WHERE is_active=1 and publish like '%2%';
result --   95 

Data in table looks like,
1,2  
2,1 
1
2

Thanks & Regards,
Prem

[pritaeas]

IN is used when comparing a column value to a list of values.

LIKE is used for string comparison.

[drjohn]

With IN , the value must match exactly an item of those listed in the list.

With LIKE, the value only has to match part of the value in the field in question.

So, sky fails for being IN (skyblue, skype, skylight, moonlight, darkblue, darksky)

but like sky% matches skyblue, skype, and skylight, and anything else in the field being tested if it starts with sky followed by zero or more characters, but does not match darksky, as sky% means the value must start with sky.

Like %sky matches darksky only from that set above

Like %sky% matches sky and any word with sky as part of it, whether there are characters before or after the sky.

[diafol]

LIKE is a pattern matcher and can do more than just use %; however, it's not as powerful as something like regex.

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

You do have other functions though, like regexp (or RLIKE)

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

but again not mega-powerful.