CODEWITHSUNDEEP
c#sqlsql-server
Shepniel Sh
Shepniel Sh

Reputation: 47

Multiple IN Clauses in SQL

I'm not sure about the functionality of the IN clause so I want to know if I can run a query safely. For what I understand when using IN clause in SQL Server creates a series of OR statements: So for example 

SELECT * FROM table WHERE column IN (1, 2, 3, 4, 5, 6)

Will be the same that:

SELECT * FROM table WHERE column = 1 OR column = 2 OR column = 3 OR column = 4 OR column = 5 OR column = 6

But what happens when something like this is executed:

SELECT * FROM table WHERE column IN (1, 2, 3) and column2 IN (4,5,6) and column 3 IN (5,7,8)

Here I want to know if for example, if some value from the first IN has two occurrences within the second one it will retrieve both results, or if it's executed in order like 1 and 4 and 5 (The first value within the IN clauses)

I want to run a query with 8 parameters that I need to check in the DB (Sending by C#) but I don't want to build a super huge query with a lot of OR like this (because it will be +90k records)

 SELECT * FROM table WHERE (column = 1 and column2 = 4 and column3 = 5 ) or 
(column = 2 and column2= 5 and column3 = 7) OR ....

Or I don't know if there is a better approach to solve this, I'm open to ideas.

Thanks

Upvotes: 1

Views: 694

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The INs are separate conditions, so the logic is equivalent to:

where (column1 = 1 or column1 = 2 or column1 = 3) and
      (column2 = 4 or column2 = 5 or column2 = 6)

Note that this is a logical equivalence. Some databases optimize IN with constant lists, for instance, by creating a binary tree for searching the values. I don't think that SQL Server does such an optimization though.

If you want "alignment", some databases -- but not SQL Server -- support tuples using IN:

where (column1, column2) in ( (1, 4), (2, 5), (3, 5) )

And this can always be represented as:

where (column1 = 1 and column2 = 4) or
      (column1 = 2 and column2 = 5) or
      (column1 = 3 and column2 = 6)

Upvotes: 7

Related Questions