FIND_IN_SET() vs. IN(): When Should I Use Each for Comma-Separated Values?

FIND_IN_SET() vs IN(): Unveiling the Query Conundrum

In the realm of database querying, understanding the nuances between different functions is crucial. One such distinction arises when comparing FIND_IN_SET() and IN(), especially when querying values stored as comma-separated strings.

The Query Discrepancy

Consider two tables: "orders" with a column "attachedCompanyIDs" containing comma-separated company identifiers and "company" with a column "companyID" and corresponding company names. The following query effectively retrieves the company names associated with an order using FIND_IN_SET():

SELECT name
FROM orders, company
WHERE orderID = 1
AND FIND_IN_SET(companyID, attachedCompanyIDs)

However, a similar query using IN() yields unexpected results:

SELECT name
FROM orders, company
WHERE orderID = 1
AND companyID IN (attachedCompanyIDs)

The Hidden Pitfall

The root cause lies in how MySQL handles comma-separated values when casting attachedCompanyIDs into an integer. The cast truncates numbers at the first non-digit, effectively reducing the string to the first comma-separated value.

For example, with attachedCompanyIDs set to '1,2,3', the IN() query incorrectly becomes:

companyID IN (1)

This explains why the IN() query only returns the first company name, while FIND_IN_SET() returns all three.

Overcoming the Limitation

To address this issue, consider alternative approaches that handle comma-separated strings appropriately. In PostgreSQL, arrays can be used:

SELECT name
FROM orders
JOIN company
ON companyID = ANY(('{' || attachedCompanyIDs || '}')::INT[])
WHERE orderID = 1

Unfortunately, arrays are not supported in MySQL. For a limited number of values (say, less than 5), a workaround is possible using cross-join and string manipulation:

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)

The above is the detailed content of FIND_IN_SET() vs. IN(): When Should I Use Each for Comma-Separated Values?. For more information, please follow other related articles on the PHP Chinese website!