'In' clause in SQL server with multiple columns

Question

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.

try this: SELECT * FROM where value_type = 1 and CODE1 IN ( 'COMM' , 'CORE' ) — Dgan
– Dgan, Commented Feb 4, 2016 at 6:34
thanks. But actually I am searching for something that can work on multiple values in both the keys. so in practical condition my first key value wont be '1' always and can be anything. and using IN for both the keys will search for Cartesian product and that wont be a correct output. — Ravi Maurya
– Ravi Maurya, Commented Feb 4, 2016 at 6:37
Your first example is a non-standard, Oracle extension. If you want to SQL that will run on other databases besides Oracle, you need separate "AND" clauses, instead of tuples: docs.oracle.com/cd/E12032_01/doc/epm.921/html_techref/maxl/dml/… — paulsm4
– paulsm4, Commented Feb 4, 2016 at 6:40
@paulsm4 The first example works in Postgres and MySQL (pretty sure). — swade
– swade, Commented Jan 25, 2019 at 22:41

Stefan Steinegger · Accepted Answer · 2016-02-04 06:47:29Z

14

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.

edited Feb 4, 2016 at 6:47

answered Feb 4, 2016 at 6:41

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ravi Maurya Over a year ago

thanks Stefan. I have millions of data records coming into my java application so doing this might be a bit hectic and time consuming for Java. but guess their is no other way so have to check on this. thanks for the help.

swade Over a year ago

This doesn't allow you to pass a list of values (tuples) for it to be dynamic. This is very rigid to only two combinations like his first example (which works in other databases).

Andrew · Accepted Answer · 2019-12-02 22:26:37Z

If you have under 1000 tuples you want to check against and you're using SQL Server 2008+, you can use a table values constructor, and perform a join against it. You can only specify up to 1000 rows in a table values constructor, hence the 1000 tuple limitation. Here's how it would look in your situation:

SELECT <table_name>.* FROM <table_name> 
JOIN ( VALUES
    ('I', 'COMM'),
    ('I', 'CORE')
) AS MyTable(a, b) ON a = value_type AND b = CODE1;

This is only a good idea if your list of values is going to be unique, otherwise you'll get duplicate values. I'm not sure how the performance of this compares to using many ANDs and ORs, but the SQL query is at least much cleaner to look at, in my opinion.

You can also write this to use EXIST instead of JOIN. That may have different performance characteristics and it will avoid the problem of producing duplicate results if your values aren't unique. It may be worth trying both EXIST and JOIN on your use case to see what's a better fit. Here's how EXIST would look,

SELECT * FROM <table_name> 
WHERE EXISTS (
    SELECT 1
    FROM (
        VALUES
            ('I', 'COMM'),
            ('I', 'CORE')
    ) AS MyTable(a, b)
    WHERE a = value_type AND b = CODE1
);

In conclusion, I think the best choice is to create a temporary table and query against that. But sometimes that's not possible, e.g. your user lacks the permission to create temporary tables, and then using a table values constructor may be your best choice. Use EXIST or JOIN, depending on which gives you better performance on your database.

Roohi Ali · Accepted Answer · 2017-11-15 07:46:12Z

3

Normally you can not do it, but can use the following technique.

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

edited Nov 15, 2017 at 7:46

answered Nov 15, 2017 at 7:40

Roohi Ali

7046 silver badges7 bronze badges

2 Comments

Andrew Over a year ago

be warned, this will miss any indexes you have on the table, unless you happen to index this pattern specifically.

DeveloperDan Over a year ago

I love the simplicity of this solution. But beware, it can lead to errors if you remove the delimiter: 'ab' + 'c' = 'a'+'bc'.

Alexei - check Codidact · Accepted Answer · 2016-02-04 06:47:05Z

2

A better solution is to avoid hardcoding your values and put then in a temporary or persistent table:

CREATE TABLE #t (ValueType VARCHAR(16), Code VARCHAR(16))

INSERT INTO #t VALUES ('I','COMM'),('I','CORE')

SELECT DT. * 
FROM <table_name> DT 
JOIN #t T ON T.ValueType = DT.ValueType AND T.Code = DT.Code

Thus, you avoid storing data in your code (persistent table version) and allow to easily modify the filters (without changing the code).

answered Feb 4, 2016 at 6:47

Alexei - check Codidact

23.2k22 gold badges159 silver badges179 bronze badges

Comments

JTR · Accepted Answer · 2016-02-04 07:28:29Z

1

I think you can try this, combine and and or at the same time.

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

answered Feb 4, 2016 at 7:28

JTR

3332 gold badges10 silver badges31 bronze badges

Comments

Marcel · Accepted Answer · 2017-06-22 07:36:32Z

0

What you can do is 'join' the columns as a string, and pass your values also combined as strings.

where (cast(column1 as text) ||','|| cast(column2 as text)) in (?1)

The other way is to do multiple ands and ors.

answered Jun 22, 2017 at 7:36

Marcel

1,77017 silver badges36 bronze badges

Comments

gladluter · Accepted Answer · 2017-12-01 10:25:33Z

0

I had a similar problem in MS SQL, but a little different. Maybe it will help somebody in futere, in my case i found this solution (not full code, just example):

SELECT Table1.Campaign
      ,Table1.Coupon
  FROM [CRM].[dbo].[Coupons] AS Table1 
  INNER JOIN [CRM].[dbo].[Coupons] AS Table2 ON Table1.Campaign = Table2.Campaign AND Table1.Coupon = Table2.Coupon
  WHERE Table1.Coupon IN ('0000000001', '0000000002') AND Table2.Campaign IN ('XXX000000001', 'XYX000000001')

Of cource on Coupon and Campaign in table i have index for fast search.

answered Dec 1, 2017 at 10:25

gladluter

1

Comments

codejunkie · Accepted Answer · 2020-12-30 20:32:59Z

0

Compute it in MS Sql

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

answered Dec 30, 2020 at 20:32

codejunkie

736 bronze badges

Collectives™ on Stack Overflow

'In' clause in SQL server with multiple columns

8 Answers 8

2 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

2 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related