Difference Between WHERE FIND_IN_SET(...)>0 and WHERE FIND_IN_SET(...)

Question

According to MySQL Reference Manual - The SET Type

Normally, you search for SET values using the FIND_IN_SET() function or the LIKE operator:

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

According to MySQL Reference Manual

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL.

Therefore, I think that

SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)

works properly when you want to find value from a set.

So why does example from MySQL Reference Manual explicitly comparing with >0 ?

Is there any difference ?

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2016-06-16T03:45:11

Consider the following two WHERE clauses:

WHERE FIND_IN_SET('value', set_col) > 0
WHERE FIND_IN_SET('value', set_col)

In the first one, if any of the entries in sel_col contains value, then the return value for FIND_IN_SET() will be some number greater than 0, otherwise it will be 0. In other words, it will return TRUE if the value is found, and FALSE otherwise.

The second WHERE clause will evaluate to either WHERE X, where X is either 1 or greater, if value be found, or 0, if value be not found. In MySQL, the value 0 is synonymous with FALSE, and a positive number of 1 or greater is synonymous with TRUE (see documentation).

So these two WHERE clauses will behave identically.

Difference Between WHERE FIND_IN_SET(...)>0 and WHERE FIND_IN_SET(...)

1 Answers