Not allowing Multiple CASE Subquery Results when using IN

Question

I'm working with the following query:

SELECT *
FROM TableA a
WHERE a.FieldA IN (

    CASE

    --select subquery returns a single value
    WHEN a.FieldB = 'Value1'
        THEN (select b.ID from TableB b where b.FK_Field = '123')

    --select subquery returns multiple values
    WHEN a.FieldB = 'Value2'
        THEN (select c.ID from TableC c where c.FK_Field = '123')

    END
)

The first case select statement returns only a single b.ID. If I just have that statement, my code works.

The second case statement, however, returns multiple c.IDs. When I add that check, I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If I would have WHERE a.FieldA =, then I understand that the subquery can only return 1 value. I however have WHERE a.FieldA IN, so why is it complaining if there are multiple values returned?

How can I implement this kind of check?

`CASE` in T-SQL is an **expression** (like `a+b`) which returns a **single, atomic value** - you **cannot** use it to selectively run SQL snippets that return entire result sets ... — marc_s, Dec 01 '16 at 19:02
@marc_s I guess then this error would fall into the category *when the subquery is used as an expression*. Do you have any suggestions of how I can do this kind of check without using `CASE` or somehow allowing multiple results? — Tot Zam, Dec 01 '16 at 19:05
Perhaps [this](http://stackoverflow.com/a/10260297/92546) answer will offer some insights. — HABO, Dec 01 '16 at 19:31

score 1 · Accepted Answer · answered Dec 01 '16 at 19:35

As @marc_s explained in a comment:

CASE in T-SQL is an expression (like a+b) which returns a single, atomic value - you cannot use it to selectively run SQL snippets that return entire result sets

In order to resolve this error, I removed the CASE statement and instead used a bunch of AND and OR statements to accomplish the same kind of check.

SELECT *
FROM TableA a
WHERE
    (a.FieldB = 'Value1'
        AND a.FieldA IN (select b.ID from TableB b where b.FK_Field = '123'))

    OR (a.FieldB = 'Value2'
        AND a.FieldA IN (select c.ID from TableC c where c.FK_Field = '123'))

This code is a bit messier than a CASE statement, but it works.

And can benefit from index too. – Deadsheep39 Dec 01 '16 at 22:32 — Deadsheep39, Dec 01 '16 at 22:32

Matt · Answer 2 · 2016-12-01T19:43:52.150

1

Lots of ways of doing this, here is one way using union all and a correlated EXISTS statement

;WITH cte AS (
    SELECT 'Value1' as FieldB, b.Id
    FROM
       TableB
    WHERE
       b.FK_FieldId = '123'

    UNION ALL

    SELECT 'Value1' as FieldB, c.Id
    FROM
       TableB
    WHERE
       c.FK_FieldId = '123'
)



SELECT *
FROM
    TableA a
WHERE
    EXISTS (SELECT 1
          FROM
             cte c
          WHERE
             a.FieldB = c.FieldB
             AND a.FieldA = c.Id)

The problem with the way you have written it is that you are getting a non-scalar value (meaning more than 1 row) where sql is expecting a scalar value. In the case expression only scalar values can be used in the THEN part as well as some rules in WHEN as well. To solve you need to break apart your case expression to multiple where statements and/or use some other technique such as the one above.

Or you could write your case expression like this:

SELECT *
FROM
    TableA a
WHERE
    (CASE
       WHEN a.FieldB = 'Value1' AND a.FieldA IN (select b.ID from TableB b where b.FK_Field = '123') THEN 1
       WHEN a.FieldB = 'Value2' AND a.FieldA IN (select c.ID from TableC c where c.FK_Field = '123') THEN 1
       ELSE 0
    END) = 1

edited Dec 01 '16 at 19:43

answered Dec 01 '16 at 19:38

Matt

13,833
2
16
28

Is there a difference in efficiency between the two methods you suggested? Or is there some other reason you would suggest I use one over the other? – Tot Zam Dec 01 '16 at 19:45
1

That will depend on your data set, but actually switching it up to use exists individually may be faster yet. http://stackoverflow.com/questions/173041/not-in-vs-not-exists The answer you posted works as well which may be faster yet you would have to test. When using IN it if very important that the list cannot contain a NULL which probably wont be a problem when using ID's but if for some reason it did contain a NULL you would get all matches because SQL can't compare the null – Matt Dec 01 '16 at 19:49
Thanks for the suggestions. I will do some testing to see which method works best for my data set. – Tot Zam Dec 01 '16 at 19:52
Very inefficient solution. Both. – Deadsheep39 Dec 01 '16 at 21:33
1

@Deadsheep39 that can depend on the number of values in TableB and TableC, and a few other things such as indexes on the tables. you may see in one of my comments that I also suggest switching to doing the exists individually as you have shown. The idea of showing the CASE expression is so that the OP gains an understanding of how he could have transformed his to not end up violating the scalar value. – Matt Dec 01 '16 at 21:41
In this particular case qry don't use index because of case statement. Qry cannot use index from TableA. In small table we don't care, but it's wrong idea. – Deadsheep39 Dec 01 '16 at 22:04

score 0 · Answer 3 · answered Dec 01 '16 at 21:29

Don't use case in predicates if it is not necessary - using case make your argument non-SARG (you qry will not use index).

SELECT *
FROM TableA a
WHERE EXISTS(
        SELECT NULL 
        FROM TableB b 
        WHERE a.FieldB = 'Value1' 
            AND b.FK_Field = '123' 
            AND a.FieldA = b.ID))
    OR EXISTS(
        SELECT NULL 
        FROM TableC c 
        WHERE a.FieldB = 'Value2' 
            AND c.FK_Field = '123' 
            AND a.FieldB = c.ID))

Use indexes. And try to make your qry readable.

Or if you would like use semi-join:

SELECT *
FROM TableA a
WHERE a.FieldA IN (
        SELECT b.ID
        FROM TableB b 
        WHERE a.FieldB = 'Value1' 
            AND b.FK_Field = '123'))
    OR a.FieldB IN (
        SELECT c.ID
        FROM TableC c 
        WHERE a.FieldB = 'Value2' 
            AND c.FK_Field = '123'))

Both of this solutions are with SARG.

Can you explain what SARG is? And I thought `CASE` statements improve efficiency since the statement terminates as soon as a match is found?? — Tot Zam, Dec 01 '16 at 21:43
SARG means search argument. Its important for making eficient qries. Shortly it means make your qry able to use index or best eficient execution plan. — Deadsheep39, Dec 01 '16 at 22:00

Not allowing Multiple CASE Subquery Results when using IN

3 Answers3