Difference between FIND_IN_SET and IN in Mysql?

You will learn how to use FIND_IN_SET and IN in this tutorial.

 

FIND_IN_SET: It is a built-in function that is used to find the position of a string within a comma-separated list of strings.

 

Syntax: FIND_IN_SET(value,list);

value: String value to search in list.

list: Comma separated string list.

 

    • FIND_IN_SET returns null if the value or list is null.
      • Ex: SELECT FIND_IN_SET(NULL,’a,b,c,d’)
      • Output: NULL
    • FIND_IN_SET returns 0 if value not found in the list.
      • Ex: SELECT FIND_IN_SET(‘g’,’a,b,c,d’)
      • Output: 0
    • FIND_IN_SET return position of the value in the list, if the value is found in the list.
      • Ex: SELECT FIND_IN_SET(‘d’,’a,b,c,d’)
      • Output: 4

IN: IN operator is used to finding value from a list of values.

Ex:  Employee table is mention below.

image

 

SELECT * FROM emp WHERE salary IN (20000,15000)

Output:

image

 

Differences:

IN:

Example1:  SELECT * FROM emp WHERE salary IN (‘15000,20000,10000’);

 

Output: 

image

if you see the table above, there are multiple values present in the table and there are three salaries in ‘15000,20000,10000’ but it is returning only one value. Why this happens. It is because when we are searching in a list of string values then it returns the first matched record only.

 

FIND_IN_SET:

Example2: SELECT * FROM emp WHERE FIND_IN_SET(salary,’15000,20000,10000′);

Output:

image

 

You can see the differences in the above two examples.

 

Summary:

  • Use FIND_IN_SET to find the position of a string from a comma-separated list of strings
  • Use IN to find value from a list of values.
  • Never use IN to find the position of a string from a comma-separated list of strings, because you will get the first element from the list.

Please provide your valuable comments and suggestions.

Thanks for reading. Happy codingSmile

Leave a Comment