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

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

LIKE is used for string comparison.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.