IN clause in sql

The SQL IN condition (sometimes called the IN operator or clause) allows us to easily test if an expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE or DELETE statement.

We can use a subquery in the IN condition

Let’s see the syntax

IN (v1,v2,v3,….vn) or IN (select C1 from t1)

See below example

I have a table DimProduct_test in this we have approx. 4 million records. Now we need to get the product details where ProductAlternateKey in

('BA-8327','BC-M005'

,'BK-M18B-44','BK-M18B-52','BK-M38S-42','BK-M68B-38','BK-M68B-42','BK-M68B-46'

,'BK-M68S-38','BK-M82B-42')

SElect * from DimProduct_Test where ProductAlternateKey in ('BA-8327','BC-M005' ,'BK-M18B-44','BK-M18B-52' ,'BK-M38S-42','BK-M68B-38' ,'BK-M68B-42','BK-M68B-46' ,'BK-M68S-38','BK-M82B-42')

See the result

   

We can also use the not in IN clause

Not IN (v1,v2,v3,….vn) or Not IN (select C1 from t1)

See the example

Get the product details where ProductAlternateKey not in

('BA-8327','BC-M005'

,'BK-M18B-44','BK-M18B-52','BK-M38S-42','BK-M68B-38','BK-M68B-42','BK-M68B-46'

,'BK-M68S-38','BK-M82B-42')