SQL Server: Case with the statement "NOT IN"

Question

I am trying to do a CASE statement in SQL Server (stored procedure) where I am supposed to check if whether or not it should get some results from another table.

I just made this up to illustrate the example (not working!)

SELECT
    *
FROM
    cards
WHERE
    CardID = @CardID
    AND
    CardID = 
        CASE WHEN @AlreadyOnDeck = 1 THEN
            IN (
                    SELECT CardID FROM OnDeckTable WHERE CardID = @CardID
            )
        CASE WHEN @AlreadyOnDeck = 0 THEN
            NOT IN (
                    SELECT CardID FROM OnDeckTable WHERE CardID = @CardID
            )
        END

I need to make the case IN / NOT IN depending on @AlreadyOnDeck variable

Solution 1

Similar to Max's answer but uses CASE statements. (May produce a slightly better execution plan due to short circuiting of the outer CASE statement.)

SELECT
    *
FROM
    cards c
WHERE
    c.CardID = @CardID
    AND
    1 = (CASE WHEN @AlreadyOnDeck = 1 THEN 
                   (CASE WHEN EXISTS(select * OnDeckTable dt where dt.CardID = c.CardID) THEN 1 END)
              WHEN @AlreadyOnDeck = 0 THEN 
                   (CASE WHEN NOT EXISTS(select * from OnDeckTable dt where dt.CardID = c.CardID) THEN 1 END)       
        END)

Solution 2

I think this will work, but haven't tested (no test data provided).

SELECT
    *
FROM
    cards
WHERE
    CardID = @CardID
    AND
    (
        exists (select 1 from OnDeckTable where CardId = @CardId and @AlreadyOnDeck = 1)
        or not exists (select 1 from OnDeckTable where CardId = @CardId and @AlreadyOnDeck = 0)
    )

Solution 3

SELECT
    *
FROM
    cards crd
WHERE
    CardID = @CardID
    AND
    (
    (@AlreadyOnDeck = 1 AND EXISTS(SELECT 1 FROM OnDeckTable dt WHERE dt.CardID = crd.CardID))
    OR
    (@AlreadyOnDeck = 0 AND NOT EXISTS(SELECT 1 FROM OnDeckTable dt WHERE dt.CardID = crd.CardID))
    )

Solution 4

I would do this with a join like so:

SELECT *
FROM cards c
LEFT JOIN OnDeckTable o ON c.CardID = o.CardID
WHERE c.CardID = @CardID
  AND (((@alreadyOnDeck = 0) AND (o.CardID is not null)) OR
      ((@alreadyOnDeck = 1) AND (o.CardID is null)))

I believe this will be faster than the sub-query exists suggestions but I have not tested.

Also note, I did not test the code above, it may have typos.

The fastest way to solve this is to encase in one if statement for the @alreadyOnDeck variable, like this:

if (@alreadyOnDeck = 1)
  -- select for this case
else
  -- select for this case.

If your code is in a stored procedure this would be fastest way, the input is not going to change during the query.

View more solutions