‘In’ clause in SQL server with multiple columns

I have a component that retrieves data from database based on the keys provided. However I want my java application to get all the data for all keys in a single database hit to fasten up things. I can use ‘in’ clause when I have only one key.

While working on more than one key I can use below query in oracle

SELECT * FROM <table_name> 
where (value_type,CODE1) IN (('I','COMM'),('I','CORE'));

which is similar to writing

SELECT * FROM <table_name> 
where value_type = 1 and CODE1 = 'COMM' 

and

SELECT * FROM <table_name> 
where value_type = 1 and CODE1 = 'CORE' 

together

However, this concept of using ‘in’ clause as above is giving below error in ‘SQL server’

ERROR:An expression of non-boolean type specified in a context where a condition is expected, near ','.

Please let know if their is any way to achieve the same in SQL server.

Answer

This syntax doesn’t exist in SQL Server. Use a combination of And and Or.

SELECT * 
FROM <table_name> 
WHERE 
  (value_type = 1 and CODE1 = 'COMM')
  OR (value_type = 1 and CODE1 = 'CORE') 

(In this case, you could make it shorter, because value_type is compared to the same value in both combinations. I just wanted to show the pattern that works like IN in oracle with multiple fields.)

---

When using IN with a subquery, you need to rephrase it like this:

Oracle:

SELECT * 
FROM foo 
WHERE 
  (value_type, CODE1) IN (
    SELECT type, code 
    FROM bar
    WHERE <some conditions>)

SQL Server:

SELECT * 
FROM foo 
WHERE 
  EXISTS (
    SELECT * 
    FROM bar 
    WHERE <some conditions>
      AND foo.type_code = bar.type 
      AND foo.CODE1 = bar.code)

There are other ways to do it, depending on the case, like inner joins and the like.