FIND IN SET vs IN

Question

I have 2 tables in my database   One is for orders  and one is for companies   Orders has this structure   OrderID           attachedCompanyIDs ------------------------------------    1                     1 2 3    2                     2 4   And Company has this structure   CompanyID               name --------------------------------------     1                 Company 1     2                 Another Company     3                 StackOverflow     4                 Nothing   To get an order s companies names  I can do a query as such   SELECT name FROM orders company WHERE orderID   1 AND FIND IN SET companyID  attachedCompanyIDs    That query works fine  but the following query does not   SELECT name FROM orders company WHERE orderID   1 AND companyID IN  attachedCompanyIDs    Why does the first query work but not the second one   The first query returns   name --------------- Company 1 Another Company StackOverflow   The second query only returns   name --------------- Company 1   Why is this  why does the first query return all the companies  but the second query only returns the first one

User · Answer

To get the all related companies name  not based on particular Id   SELECT       SELECT GROUP CONCAT cmp cmpny name       FROM company cmp      WHERE FIND IN SET cmp CompanyID  odr attachedCompanyIDs        AS COMPANIES FROM orders odr

User · Answer

Let me explain when to use FIND IN SET and When to use IN   Let s take table A which has columns named  aid   aname   Let s take table B which has columns named  bid   bname   aids    Now there are dummy values in Table A and Table B as below   Table A  aid  aname  1    Apple  2    Banana  3    Mango  Table B  bid  bname   aids  1    Apple   1 2  2    Banana  2 1  3    Mango   3 1 2  enter code here   Case1  if you want to get those records from table b which has 1 value present in aids columns then you have to use FIND IN SET   Query  select   from A JOIN B ON FIND IN SET A aid b aids  where A aid   1    Case2  if you want to get those records from table a which has 1 OR 2 OR 3  value present in aid columns then you have to use IN   Query  select   from A JOIN B ON A aid IN  b aids    Now here upto you that what you needs through mysql query

User · Answer

attachedCompanyIDs is one big string  so mysql try to find company in this its cast to integer  when you use where in  so if comapnyid   1     companyID IN   1 2 3     this is return true  but if the number 1 is not in the first place   companyID IN   2 3 1     its return false

User · Answer

SELECT o    GROUP CONCAT c name  FROM Orders AS o   Company c     WHERE FIND IN SET c CompanyID   o attachedCompanyIDs  GROUP BY o attachedCompanyIDs

User · Answer

because the second query is looking for rows with the id s 1 OR 2 OR 3  the first query is looking for a one of the comma delimited values to exist in companyID   and another problem here is you aren t joining the tables on a common key in your where so you are going to get a mutation of rows that   count table1    count table2    Your problem really exists with part 2 of my answer   with your second query

User · Answer

SELECT  name FROM    orders company WHERE   orderID   1         AND companyID IN  attachedCompanyIDs    attachedCompanyIDs is a scalar value which is cast into INT  type of companyID    The cast only returns numbers up to the first non-digit  a comma in your case    Thus   companyID IN   1 2 3     companyID IN  CAST  1 2 3  AS INT     companyID IN  1    In PostgreSQL  you could cast the string into array  or store it as an array in the first place    SELECT  name FROM    orders JOIN    company ON      companyID   ANY         attachedCompanyIDs         INT    WHERE   orderID   1   and this would even use an index on companyID   Unfortunately  this does not work in MySQL since the latter does not support arrays   You may find this article interesting  see  2     10 things in MySQL  that won   t work as expected    Update   If there is some reasonable limit on the number of values in the comma separated lists  say  no more than 5   so you can try to use this query   SELECT  name FROM    orders CROSS JOIN                   SELECT  1 AS pos         UNION ALL         SELECT  2 AS pos         UNION ALL         SELECT  3 AS pos         UNION ALL         SELECT  4 AS pos         UNION ALL         SELECT  5 AS pos           q JOIN    company ON      companyID   CAST NULLIF SUBSTRING INDEX attachedCompanyIDs       -pos   SUBSTRING INDEX attachedCompanyIDs       1 - pos   AS UNSIGNED

[mysql] FIND_IN_SET() vs IN()

Examples related to mysql