T-SQL IN Operator not working

Question

T-SQL IN Operator not working

vj78 21

SELECT * FROM Table a
WHERE column1 IN (SELECT Column2 FROM Table2 b)

This works if I use TOP 1
SELECT * FROM Table a
WHERE column1 IN (SELECT TOP 1 Column2 FROM Table2 b)

EchoLiu-MSFT 14,621 Reputation points

2021-08-05T08:30:18.757+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

9 answers

Your answer

EchoLiu-MSFT 14,621 Reputation points

2021-08-05T08:30:18.757+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer 1

vj78 21

error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= pr when the subquery is used as an expression.

Ryan Abbey 1,186 Reputation points

2021-07-29T23:31:24.973+00:00

Based on your error, are you 100% certain you are using "IN"? Can you try "= ANY" instead of "IN"
vj78 21 Reputation points

2021-07-30T11:10:23.34+00:00

Yes, I tried both = and IN but same error message.
Ryan Abbey 1,186 Reputation points

2021-08-01T19:38:20.783+00:00

Not "=" but "= ANY" the "ANY" is important

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

This works for me:

CREATE TABLE #t1 (a int NOT NULL)
INSERT #t1 (a) VALUES (1), (2), (3), (4)
CREATE TABLE #t2 (n int NOT NULL)
INSERT #t2 (n) VALUES (1), (2), (13), (14)
SELECT a.a FROM #t1 a WHERE a.a IN (SELECT b.n FROM #t2 b)
go
DROP TABLE #t1, #t2

I guess your actual query looks different, but it is difficult to troubleshoot a query we don't see.

Answer 3

vj78 21

DECLARE @alenzi nvarchar(max) = N'

(

SELECT * FROM Table a
WHERE column1 IN (SELECT Column2 FROM Table2 b)

)

EXEC (@alenzi )

It is hitting a linked server on db2

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-07-29T15:35:16.84+00:00

We still don't see the full statement.

If it is a linked server, it is possible that the OLE DB provider creates a non-working query on the linked server. One option is to run Profiler on the remote server to see what that might be,.

What does "SELECT @@version" return on the two servers?
Tom Phillips 17,771 Reputation points

2021-07-29T17:40:01.743+00:00

You are not showing us exactly what you are doing. There is nothing wrong with the code you posted.

Please give us the actual code you are running.
vj78 21 Reputation points

2021-07-29T18:14:24.427+00:00
DECLARE @itemnumbers table

(

ID varchar(1000)

)

insert into @itemnumbers values ('1')

insert into @itemnumbers values ('2')

insert into @itemnumbers values ('3')

insert into @itemnumbers values ('4')

insert into @itemnumbers values ('5')

insert into @itemnumbers values ('6')

DECLARE @alenzi nvarchar(MAX) = N'

SELECT * FROM OPENQUERY ( DB2System, N'' SELECT * FROM table1 a WHERE column1 IN ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + ''''' '' )'

EXEC (@alenzi )

vj78 21

DECLARE @itemnumbers table

(

ID varchar(1000)

)

insert into @itemnumbers values ('1)

insert into @itemnumbers values ('2')

insert into @itemnumbers values ('3')

DECLARE @alenzi nvarchar(MAX) = N'

   SELECT * FROM OPENQUERY  

   (  

          DB2System, N''  

          SELECT  *  

          FROM  

                 table1 a  

                 WHERE column1    IN  ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''  

                                 

                  

                 ''  

          )'

EXEC (@alenzi )

vj78 21

DECLARE @itemnumbers table

(

ID varchar(1000)

)

insert into @itemnumbers values ('1)

insert into @itemnumbers values ('2')

insert into @itemnumbers values ('3')

DECLARE @alenzi nvarchar(MAX) = N'

   SELECT * FROM OPENQUERY  

   (  

          DB2System, N''  

          SELECT  *  

          FROM  

                 table1 a  

                 WHERE column1    IN  ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''  

                                 

                  

                 ''  

          )'

EXEC (@alenzi )

vj78 21

DECLARE @itemnumbers table

(

ID varchar(1000)

)

insert into @itemnumbers values ('1)

insert into @itemnumbers values ('2')

insert into @itemnumbers values ('3')

DECLARE @alenzi nvarchar(MAX) = N'

   SELECT * FROM OPENQUERY  

   (  

          DB2System, N''  

          SELECT  *  

          FROM  

                 table1 a  

                 WHERE column1    IN  ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''  

                                 

                  

                 ''  

          )'

EXEC (@alenzi )

vj78 21

DECLARE @itemnumbers table

(

ID varchar(1000)

)

insert into @itemnumbers values ('1)

insert into @itemnumbers values ('2')

insert into @itemnumbers values ('3')

DECLARE @alenzi nvarchar(MAX) = N'

   SELECT * FROM OPENQUERY  

   (  

          DB2System, N''  

          SELECT  *  

          FROM  

                 table1 a  

                 WHERE column1    IN  ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''  

                                 

                  

                 ''  

          )'

EXEC (@alenzi )

vj78 21 Reputation points

2021-07-30T11:13:13.073+00:00

I am running the query using Management Studio on my local machine.
One of the servers - I checked and the version returned is 2014.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-07-30T11:15:43.217+00:00

If you don't answer the questions we ask you, we will not be able to help you.

We need to see the full query text. We need to see "SELECT @@version" from both servers.

And we ask for more questions after that.

Answer 4

DECLARE @itemnumbers table

(

ID varchar(1000)

)

insert into @itemnumbers values ('1')

insert into @itemnumbers values ('2')

insert into @itemnumbers values ('3')

insert into @itemnumbers values ('4')

insert into @itemnumbers values ('5')

DECLARE @alenzi nvarchar(MAX) = N'

   SELECT * FROM OPENQUERY  

   (  

          DB2System, N''  

          SELECT  *  

          FROM  

                 Table1 a  

                 WHERE column1 IN  ''''' + (SELECT ID FROM @itemnumbers ) + '''''  

                                 

                  

                 ''  

          )'

EXEC (@alenzi )

Answer 5

DECLARE @itemnumbers table

(

ID varchar(1000)

)

insert into @itemnumbers values ('1)

insert into @itemnumbers values ('2')

insert into @itemnumbers values ('3')

DECLARE @alenzi nvarchar(MAX) = N'

   SELECT * FROM OPENQUERY  

   (  

          DB2System, N''  

          SELECT  *  

          FROM  

                 table1 a  

                 WHERE column1    IN  ''''' + (SELECT TOP 1 ID FROM @itemnumbers ) + '''''  

                                 

                  

                 ''  

          )'

EXEC (@alenzi )

Share via

T-SQL IN Operator not working

9 answers

Your answer