difference between where_in and find_in_set

Question

I am working on a join query in which I have to get the data from a column which contain the comma separated values.like allowed_activity contain 1,2,3,4,5,6 this the activity_id which is allowed.

So In the query, I am checking that current activity is allowed or not and for that, I have used where_in and also tried find_in_set in where condition.

Here is that part of the query:

$this->db->where_in('a.allowed_activity',$activity_id);
//And With FIND_IN_SET
$this->db->where("FIND_IN_SET($activity_id, a.allowed_activity) !=",0);

Problem or Confusion

When I use where_in it doesn't give me the result. But if I use FIND_IN_SET then it will return the desired result. I thought that where_in can do the task but it didn't.

Just Want to know why find_in_set working differently than where_in Because of both the function work same as finding the data is in comma separated list.

Here is the complete query:

SELECT mycolumns
FROM `table1` as `ea`
LEFT OUTER JOIN `table2` as `a` ON `ea`.`package_id` = `a`.`id`
LEFT OUTER JOIN `table3` as `e` ON `ea`.`employer_id` = `e`.`id`
WHERE `ea`.`card_id` > 0
AND FIND_IN_SET(6, a.allowed_activity) !=0
AND `ea`.`id` = '6'
ORDER BY `ea`.`creation_date` DESC

I am using Codeigniter Active record.

score 2 · Accepted Answer · answered Aug 17 '17 at 07:52

WHERE IN requires the set of values to be specified literally in the query, not as a single value containing a comma-delimited string. If you write:

WHERE 6 IN (a.allowed_activity)

it will treat a.allowed_activity as just a single value, and compare it with 6, not as a set of multiple values to search.

FIND_IN_SET searches a comma-delimited string for the value.

Another way to view it is that IN is a shortcut for a bunch of = tests combined with OR:

WHERE x IN (a, b, c, d)

is short for

WHERE x = a OR x = b OR x = c OR x = d

When you rewrite it like this, you can see clearly why it won't work with a column containing a comma-delimited string. It simply translates

WHERE 6 IN (a.allowed_activity)

to:

WHERE 6 = a.allowed_activity

score 1 · Answer 2 · answered Aug 17 '17 at 07:49

1

The following code is incorrect:

$this->db->where("FIND_IN_SET($activity_id, a.allowed_activity) !=",0);

I'm surprised this even runs without error, but one possibility is that it is generating a WHERE clause which compares a string against 0. Of course, they will never be equal, and so this will always be true.

If you want to call the native FIND_IN_SET() function, then you would need to use whereRaw():

$this->db->whereRaw("FIND_IN_SET($activity_id, a.allowed_activity) !=0");

Using WHERE IN (...) is incorrect here, because you have all the values in a single row, separated from commas. WHERE IN would make sense if you wanted to filter for certain values from many rows, each column having a single value.

answered Aug 17 '17 at 07:49

Tim Biegeleisen

502,043
27
286
360

`whereRaw` is not making any difference sir, it gives the same result as `where` @TimBiegeleisen – always-a-learner Aug 17 '17 at 08:19
Update your question with the full Laravel query you are running. Whence is that `a` alias you are using? My guess is that your code is the problem. – Tim Biegeleisen Aug 17 '17 at 08:23
Sir, I am using CodeIgniter – always-a-learner Aug 17 '17 at 08:24

score 1 · Answer 3 · answered Aug 17 '17 at 10:15

FIND_IN_SET search for a value in a array and return the index of the value then if the function is different than 0 this mean that the value in present in the list . so the code

  this->db->where("FIND_IN_SET(6, a.allowed_activity) !=",0);

mean return the index of the value 6 in a.allowed_activity
and if the value return is different that 0 then the value 6 is present in a.allowed_activity

WHERE ... IN check if the value in the set but don't return the index .. just return true or false if the value is present or not

difference between where_in and find_in_set

Problem or Confusion

3 Answers3

Linked