sql query in "WHERE IN" clause

Question

select * from tablename 
     where id in(select id from tablename2 where condition UNION select -1)

Is it ok to use select -1 as if the inner query does not result anything it will give error. It is feasible or not?

If the inner query doesn't return anything, there just won't be a row returned in the outer query. What error are you getting? — Jacob
– Jacob, Commented Aug 9, 2011 at 11:39
I don't think it will result in error if inner query does not return any value. — Harry Joy
– Harry Joy, Commented Aug 9, 2011 at 11:40
got it.. if we kept in() blank then only it gives error. if inner query returns empty query will returns empty result.Thanks anyways — jit
– jit, Commented Aug 9, 2011 at 12:12

ajreal · Accepted Answer · 2011-08-09 11:41:36Z

1

imho, inner-select is far from ideal (slow)

based on your posted SQL, an inner join will do the trick

select * 
from tablename as t1
inner join tablename2 as t2
on t1.id=t2.id
where condition; --- your condition

answered Aug 9, 2011 at 11:41

ajreal

47.4k11 gold badges98 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Skorpioh · Accepted Answer · 2011-08-09 11:59:03Z

1

If you have to get it done with a subquery then the correct way to do it would probably be:

SELECT *
FROM tablename AS t1
WHERE EXISTS
    (SELECT id 
     FROM tablename2 AS t2
     WHERE conditions)

answered Aug 9, 2011 at 11:59

Skorpioh

1,3551 gold badge12 silver badges31 bronze badges

GolezTrol · Accepted Answer · 2011-08-09 11:41:06Z

0

It won't give an error if the query returns nothing. It just returns an empty resultset.

answered Aug 9, 2011 at 11:41

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

got it.. if we kept in() blank then only it gives error. if inner query returns empty query will returns empty result.Thanks anyways