[mysql] FIND_IN_SET() vs IN()

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?

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.

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

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

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)

SELECT o.*, GROUP_CONCAT(c.name) FROM Orders AS o , Company.c
    WHERE FIND_IN_SET(c.CompanyID , o.attachedCompanyIDs) GROUP BY o.attachedCompanyIDs