Search through a column using IN

Question

I am trying to use the "IN" SQL operator but instead of a set number of defined variables, I'm trying to use a table column instead. So I've written the following, but I cannot get it work.

SELECT * FROM searchWithinTable
WHERE searchBy IN (Test2C.col1)

Any help is appreciated, Thanks! (I'm using SQL Server)

score 5 · Accepted Answer · answered Jun 18 '15 at 15:37

5

You can't pass "column" as a list for in condition, but you can pass subselect from that table/column.

Just use something like this:

SELECT * FROM searchWithinTable
WHERE searchBy IN (select col1 from Test2C)

answered Jun 18 '15 at 15:37

Andrey Korneyev

26,353
15
70
71

1

You should always use **where is not null** to avoid incorrect result. – Anuj Tripathi Jun 18 '15 at 15:39
I feel so stupid. This works, thank you very much, and again I feel rather stupid for even asking this when I look at the solution... – BitwiseStarter Jun 18 '15 at 15:39
@anujtripathi thank you for the contribution, that helps too! – BitwiseStarter Jun 18 '15 at 15:42
@anujtripathi not in this case. Possible null values returned by subquery will not affect where clause since `null in (null)` condition is false until you set `ANSI_NULLS off` option for database. – Andrey Korneyev Jun 18 '15 at 15:45
3

@BitwiseStarter: another option: `WHERE EXISTS(SELECT 1 FROM Test2C WHERE col1 = searchBy) ` – Tim Schmelter Jun 18 '15 at 15:45

score 0 · Answer 2 · answered Nov 22 '19 at 16:20

0

use sub query instead of static value

example:=

SELECT EMPID, ENAME
FROM EMPLOYEES 
WHERE EMPID IN (SELECT EMPID FROM EMPLOYEES WHERE EMPID >200)

answered Nov 22 '19 at 16:20

ᎮᏒᏗᏉᏋᏋᏁ

31
2

Search through a column using IN

2 Answers2