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 built-in function which 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 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 list.
      • Ex: SELECT FIND_IN_SET(‘g’,’a,b,c,d’)
      • Output: 0
    • FIND_IN_SET return position of value in list, if value is found in list.
      • Ex: SELECT FIND_IN_SET(‘d’,’a,b,c,d’)
      • Output: 4

IN: IN operator is used to find value from 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 table and there are three salary in ‘15000,20000,10000’ but it is returning only one value. Why this happens. It is because when we are searching in list of string value then it returns 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 above two example.

 

Summary:

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

Please provide your valuable comments and suggestions.

Thanks for reading. Happy codingSmile

Be the first to comment

Leave a Reply

Your email address will not be published.


*