FIND_IN_SET() vs. IN(): Why Does One Return All Results While the Other Only Returns the First?

FIND_IN_SET() vs IN(): Understanding the Discrepancy in Query Results

When querying relational databases, it is necessary to comprehend the distinction between the FIND_IN_SET() and IN() functions. This article examines their behavior in a specific database scenario.

Problem:

Consider two tables: orders and company. The orders table includes an attachedCompanyIDs column that holds a comma-separated list of company IDs. The company table contains the actual company names. When performing a query to retrieve company names associated with an order using the FIND_IN_SET() function, it returns all expected results. However, replacing FIND_IN_SET() with IN() in the query returns only the first company name.

Cause:

The IN() function interprets attachedCompanyIDs as a single integer, whereas FIND_IN_SET() treats it as a string. When attachedCompanyIDs is cast as an integer, it retains only the digits up to the first non-numeric character (the comma). Consequently, the IN() query only returns the company associated with the first number in the comma-separated list.

Solution:

To circumvent this issue, PostgreSQL offers a more robust solution:

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

This query leverages the ANY() function to cast attachedCompanyIDs as an array, enabling the effective use of an index on companyID.

Update:

For MySQL databases, which lack array support, an alternative approach can be employed:

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);

This query iterates through potential positions, extracting substrings from attachedCompanyIDs to match companyID. It involves multiple joins, but it remains functional for scenarios with a limited number of company IDs in the comma-separated list.

The above is the detailed content of FIND_IN_SET() vs. IN(): Why Does One Return All Results While the Other Only Returns the First?. For more information, please follow other related articles on the PHP Chinese website!