sql select statement inside case

Question

let say i wanna create something like this

select t.*,
       (case when (select name from table2 where idtable2 = t.idtable1)= NULL then '0'
        end) as result
from table1 t

how can i do it? thank you

sorry my mistakes,yes that statement it works..but it doesn't works if there is subquery before the case statement..

select t.*,(select name from table3 where idtable3 = t.idtable3)as nametable3, (case when (select name from table2 where idtable2 = t.idtable1)= NULL then '0' end) as result from table1 t

that should work, but may need a `LIMIT 1`; but it is usually (not always) faster to just JOIN the two tables and use the value directly. — Uueerdo, Sep 15 '16 at 22:49
A correlated subquery in the SELECT list of a query that's returning every row in the table. If table1 contains one million rows, that subquery is going to be executed one million times. *shudder*. ( I so want to put my pinky finger to the corner of my mouth, Dr. Evil style, when I say... "one meeelyon rows".) — spencer7593, Sep 15 '16 at 22:57

score 3 · Answer 1 · answered Sep 15 '16 at 22:52

I think you want exists:

select t.*,
       (case when not exists (select 1
                              from table2 t2
                              where t2.idtable2 = t.idtable1
                             )
             then '0'
        end) as result
from table1 t;

Alternatively, your query will work with is null:

select t.*,
       (case when (select t2.name
                   from table2 t2
                   where t2.idtable2 = t.idtable1
                  ) is null
             then '0'
        end) as result
from table1 t;

This assumes that the subquery returns one row.

score 0 · Answer 2 · answered Sep 16 '16 at 12:54

Assuming the idtable2 value for table2 is always unique, you could do a left join against table2, instead of a subquery.

CREATE TABLE #table1
(
    idtable1 INT
    ,someValue varchar(25)
)

CREATE TABLE #table2
(
    idtable2 INT
    ,name varchar(25)
)


INSERT INTO #table1 values(1,'a'),(2,'b'),(3,'c'),(4,'d')
INSERT INTO #table2 values(1,'Bob'),(2,'Kim'),(3,'Fred'),(5,'Sally')

SELECT t.*
    ,CASE 
        WHEN t2.NAME IS NULL
            THEN '0'
        END AS Result
FROM #table1 t
LEFT JOIN #table2 t2
    ON t.idtable1 = t2.idtable2

sql select statement inside case

2 Answers2