May 19, 2011 at 11:41 pm
I would like to know if there is way to construct a select query with "IN" syntax referencing a table rather then values.
For example:
Select * from Mytable
where MYValue IN (1,2,3,4,.etc) --- Because I can literally have 100's of values, I would instead
instead I would like to construct the query
Select * from Mytable
where MYValue IN (Column1 of Table2)
Is this possible.
thanks
May 20, 2011 at 12:18 am
Why not use a JOIN ?
If JOIN is a prohibited substance (:-D) , then u can use something like
SELECT *
FROM MainTable
WHERE col1 in ( select col1 from SubTable)
May 20, 2011 at 12:22 am
Certainly is and you were nearly there with what you wrote..
Select ... from T1
where col1 in (select col2 from T2)
or NOT IN as appropriate.
Some associated things to be aware of.
a) It is very good practice to qualify the name of the column in the subquery (The select from T2 in the example) . This is because if col2 does NOT exists as a valid column name in T2 but does exists in T1 you end up with a syntactically valid query but not doing what you thought.
b) Be wary of NULL values in col2 when using NOT IN. If col2 is nullable and you get a NULL returned you end up with logic that compares col1 with a NULL and returns everything.
Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply