Subquery returns more than 1 row when subselecting in a CASE WHEN statement

First query

This query works fine and the engine does not complain about it

SELECT id 
FROM agencies
WHERE id IN ((SELECT id FROM agencies))
ORDER BY id;

开发者_如何学运维

Second query

This one does not work, the engine is complaining about Subquery returns more than 1 row, when -according to me- I am doing the exact same thing when @param_1 IS NULL

SELECT 
  @param_1 := NULL,

SELECT id 
FROM agencies
WHERE id IN (CASE WHEN @param_1 IS NULL THEN (SELECT id FROM agencies) ELSE 1 END )
ORDER BY id;

Does anybody see why the engine is complaining about the second query when it isnt for the first query ?

Thanks in advance,

---

CASE expects a scalar, single value. Not a record set.

SELECT id 
FROM agencies
WHERE id IN (
        SELECT id FROM agencies WHERE @param_1 IS NULL
        UNION ALL
        SELECT 1 WHERE @param_1 IS NOT NULL
        )
ORDER BY id;

OR

SELECT id 
FROM agencies
WHERE id IN (SELECT id FROM agencies)
     AND @param_1 IS NULL
UNION ALL
SELECT id 
FROM agencies
WHERE @param_1 IS NOT NULL AND id = 1
ORDER BY id;

Another choice is to use IF

IF @param_1 IS NULL
    SELECT id 
    FROM agencies
    WHERE id IN (SELECT id FROM agencies)
    ORDER BY id;
ELSE
    SELECT id 
    FROM agencies
    WHERE id = 1
    ORDER BY id;

---

Change to:

SELECT id 
FROM agencies
WHERE id IN (
                SELECT id
                FROM agencies
                WHERE @param_1 IS NULL

                UNION ALL

                SELECT 1 as id
                WHERE @param_1 IS NOT NULL
)
ORDER BY id;