CODEWITHSUNDEEP
sql-servert-sql
Tong
Tong

Reputation: 755

SQL WHERE IN equivalent but with AND instead of OR

I came across a post that said

whereas IN ('val1', 'val2', 'val3') is basically equivalent to

WHERE
(
   column = 'val1'
   OR
   column = 'val2'
   OR 
   column = 'val3'
)

My question is, is there something that replaces the ORs with ANDs such as:

WHERE
(
   column = 'val1'
   AND
   column = 'val2'
   AND 
   column = 'val3'
)

Upvotes: 2

Views: 1407

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Not exactly. This problem can often be solved using aggregation:

select id  -- of some sort
from t
where column in ('val1', 'val2', 'val3')
group by id
having count(*) = 3;  -- all values accounted for

This assumes that column does not have duplicated values for a given id. If that is possible, use having count(distinct column) = 3.

Upvotes: 6

Aaron Dietz
Aaron Dietz

Reputation: 10277

Technically, no. Because a single row column can't have 3 different values in it at once.

I suspect you want to return only records that have only rows with those 3 specific values, but that's just a guess. Gordon's answer is a good solution if that's the case.

Upvotes: 4

Related Questions