SQL Where IN with CASE

Question

I have a where clause as such

    ... Where TruckID IN (select VanID from Vantbl)

I like to use a case so it can be smart enough

I have the folloiwng

     ...  Where TruckID IN 
       CASE WHEN @VehicleType = 'VAN'
        THEN (select VanID from Vantbl)
      CASE WHEN @VehicleType = 'SUV'
       THEN (select SUVID from SUVtbl)
     END

but does nto seem to work. Any way I can get it to work as such.

Which type of SQL are you using? MySQL? – simchona Feb 21 '12 at 22:29 — simchona, Feb 21 '12 at 22:29

score 5 · Accepted Answer · answered Feb 21 '12 at 22:28

5

I'd recommend an EXISTS instead of an IN:

WHERE EXISTS (
    SELECT 1
    FROM Vantbl
    WHERE @VehicleType = 'VAN' AND TruckID = VanID
) OR EXISTS (
    SELECT 1
    FROM SUVtbl
    WHERE @VehicleType = 'SUV' AND TruckID = SUVID 
)

answered Feb 21 '12 at 22:28

Michael Fredrickson

36,839
5
92
109

+1 Also easier to understand when you read the code (rather than left join with null check) – Magnus Feb 21 '12 at 22:32
@BicycleDude Other than some [subtleties regarding NULLs](http://stackoverflow.com/a/9344100/533120) (which is probably not relevant here), there is no compelling reason to prefer EXISTS over IN or vice-versa. Just use whichever expresses your intent best. – Branko Dimitrijevic Feb 21 '12 at 22:40
@BrankoDimitrijevic Depends on type of database. @MichaelFredrickson you _might_ get little better performance if you exchange your line `) OR EXISTS (` with `UNION ALL` – Magnus Feb 21 '12 at 22:45
@Magnus Query optimizers are very smart these days. If two queries express the **same** thing, a decent DBMS will produce the same execution plan, regardless of the syntax used. There may be an odd corner case here and there, but: (1) you can't generally say that one syntax is better than the other unless you have tested it under particular conditions on specific DBMS and (2) people often rewrite their queries falsely thinking they kept the meaning, and then attribute performance difference to the syntactic differences, where in fact it is the _semantics_ of the query that made the difference. – Branko Dimitrijevic Feb 21 '12 at 22:55
@BrankoDimitrijevic I Agree. As your point 1 pretty much is my previous comment. Personally I always use `Exists` for the reason that I feel it is easier to read. – Magnus Feb 21 '12 at 23:09
@BrankoDimitrijevic in some databases `EXISTS` can be better in performance over `IN`. This is discussed here: http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance – Stephen Quan Feb 22 '12 at 03:15
@BicycleDude You should have looked at the [second answer](http://stackoverflow.com/a/3460433/533120) as well. Quote: _"I've done some testing on SQL Server 2005 and 2008, and on both the EXISTS and the IN come back with the exact same actual execution plan, as other have stated. The Optimizer is optimal. :)"_ Like I said, there may be edge cases, but in general assume equivalent expressions will be executed equivalently, _unless_ you have actually performed tests and proven otherwise. I suspect these edge cases will be few and far between on any reasonably modern DBMS... – Branko Dimitrijevic Feb 22 '12 at 03:35

Matthias Meid · Answer 2 · 2012-02-22T08:00:55.760

1

Try this, assuming Trucks has zero or one Vantbl/SUBtbl records.

SELECT *
  FROM Trucks t
    LEFT JOIN Vantbl v ON v.VanID = t.TruckID
    LEFT JOIN SUVtbl s ON s.SUVID = t.TruckID
  WHERE (@VehicleType = 'VAN' AND v.VanID IS NOT NULL)
    OR (@VehicleType = 'SUV' AND s.SUVID IS NOT NULL)

edited Feb 22 '12 at 08:00

answered Feb 21 '12 at 22:30

Matthias Meid

12,455
7
45
79

2

Possible to generate wrong results if v-t or s-t is not 1-to-1. – RichardTheKiwi Feb 21 '12 at 22:55

score 0 · Answer 3 · answered Feb 21 '12 at 22:29

0

I don't know about CASE but have you thought of using UNION ALL ?

(
SELECT ...
FROM  ...
INNER JOIN VanTbl ON ...
WHERE @VehicleType = 'VAN'
)
UNION ALL
(
SELECT ...
FROM ...
INNER JOIN SUVtbl ON ...
WHERE @VecicleType = 'SUV'
);

answered Feb 21 '12 at 22:29

Stephen Quan

21,481
4
88
75

Branko Dimitrijevic · Answer 4 · 2012-02-21T22:45:09.817

0

No need for CASE where the basic Boolean logic would do:

...
WHERE
    (@VehicleType = 'VAN' AND TruckID IN (SELECT VanID FROM Vantbl))
    OR (@VehicleType = 'SUV' AND TruckID IN (SELECT SUVID FROM SUVtbl))

(Assuming Vantbl.VanID and SUVtbl.SUVID cannot be NULL.)

edited Feb 21 '12 at 22:45

answered Feb 21 '12 at 22:34

Branko Dimitrijevic

50,809
10
93
167

SQL Where IN with CASE

4 Answers4