CODEWITHSUNDEEP
mysqlsqlselect
Hacker
Hacker

Reputation: 7906

Why find_in_set works but IN clause

I have code like this.

DELIMITER $$
    CREATE PROCEDURE `sp_deleteOrderData`(orderid BIGINT(11))
    BEGIN
        DECLARE shipmentnumbers VARCHAR(1000);
        DECLARE cartonid VARCHAR(1000);

        SELECT GROUP_CONCAT(a_shipmentid) FROM t_shipment WHERE a_orderid = orderid INTO shipmentnumbers;

        SELECT GROUP_CONCAT(a_cartonid) FROM t_carton WHERE a_shipmentid IN (shipmentnumbers) INTO cartonid;

    SELECT shipmentnumbers;
    /*SELECT  cartonid;  */
    END$$

    DELIMITER ;

Here shipmentnumbers returns 100020,100021,100022 Ideally cartonid should be returned as 11,12,13

But i get only 11 as cartonid.

But when i use below code i get proper result.

  SELECT GROUP_CONCAT(a_cartonid) FROM t_carton WHERE FIND_IN_SET( a_shipmentid, shipmentnumbers ) INTO cartonid;

I wanted to know what exactly is the difference between IN and FIND_IN_SET and hwo di i decide what to use.

Upvotes: 3

Views: 1935

Answers (2)

Quassnoi
Quassnoi

Reputation: 425683

IN accepts a list or parameters to search, FIND_IN_SET accepts a string parameter containing a comma-separated list:

SELECT  1 IN (1, 2, 3, 4)

SELECT  FIND_IN_SET(1, '1,2,3,4')

If you try to apply IN to a comma-separated string, it will treat it as a single parameter and will match it as a whole:

SELECT  1 IN ('1,2,3,4')

Of course, the string '1' is not equal to the string '1,2,3,4' so the query above returns false.

Upvotes: 2

John Woo
John Woo

Reputation: 263843

FIND_IN_SET searches a string inside a set of string separated by a comma.

But you don't need to use GROUP_CONCAT to concatenate the rows to be used in the IN clause, try this,

SELECT  GROUP_CONCAT(a_cartonid) 
FROM    t_carton
WHERE   a_shipmentid IN
         (
            SELECT a_shipmentid
            FROM t_shipment 
            WHERE a_orderid = orderid
         )

or use JOIN

SELECT  GROUP_CONCAT(DISTINCT a.a_cartonid) 
FROM    t_carton a
        INNER JOIN
        (
            SELECT a_shipmentid
            FROM t_shipment 
            WHERE a_orderid = orderid
        ) b ON a.a_shipmentid = b.a_shipmentid

Upvotes: 1

Related Questions