compare value with 2 different columns using the IN operator

January 5, 2022

I have a situation where I need to compare the value of a column with 2 columns from my settings table.

Currently I have this query which works

declare @t int = 3
select 1
where  @t = (select s.RelationGDGMID from dbo.tblSettings s )
       or 
       @t = (select s.RelationGTTID from dbo.tblSettings s )

But I wonder if I can make this without reading tblSettings 2 times, and then I tried this

declare @t int = 3
select 1 
where  @t in (select s.RelationGDGMID, s.RelationGTTID from dbo.tblSettings s )

and this does not compiles, it returns

Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS

So how can I do this without reading tblSettings 2 times, well one solution would be using the EXISTS like the error hints me

declare @t int = 3
select 1 
where  exists (select 1 from dbo.tblSettings s where s.RelationGDGMID = @t or s.RelationGTTID = @t)

and yes that works, only reads tblSettings once, so I can use this.
But I still wonder if there is a way to make it work with the IN operator
After all, when I do this

declare @t int = 3
select 1 
where @t in (3, 1)

that works without problems,
so why does

where  @t in (select s.RelationGDGMID, s.RelationGTTID from dbo.tblSettings s )

not works, when in fact it also returns (3, 1) ?

>Solution :

One way to do it would be to use UNION if the columns are of the same type.

where @t in (select s1.RelationGDGMID from dbo.tblSettings s1 UNION
select s2.RelationGTTID from dbo.tblSettings s2)

The reason this works is because it is returning one value set (1 column with values). The reason where @t in (3, 1) works is because this the same, it is returning one value set (value 3 and value 1).

That said I would prefer the EXISTS over IN as this could produce a better query plan.