0

I know that there is a limit of 1000 items in the SQL IN clause. Currently, I am using a comma separated string of items in "IN" clause of a select query and I want to change it.

So, I found this, which states to use either JOIN or pass a query inside IN itself.

Which one should be preferred choice ?

EDIT (Another Question):

What is the difference in passing query in "IN" clause and passing comma separated parameters in "IN" clause ?

Community
  • 1
  • 1
Hardik Mishra
  • 14,779
  • 9
  • 61
  • 96
  • 4
    The database optimizer will execute both variants in the same way. You can use the one that looks better. :) – Andomar Jul 24 '12 at 06:33
  • 2
    Are you **actually** using a query with 1,000 comma-separated items in an `in` clause? 0_0 Srsly, put them in a table and join appropriately. –  Jul 24 '12 at 06:52
  • @JackManey: Not now but in future there are chances. – Hardik Mishra Jul 24 '12 at 08:11
  • @HardikMishra - In that case, put them in a table and join appropriately (or use an `in` statement such as `column in (select column from table_with_tons_of_values_oh_god_dont_want_to_list_them_all)`). –  Jul 24 '12 at 08:12
  • @Andomar:Please paste your comment as answer. I found it helpful – Hardik Mishra Jul 25 '12 at 11:39

2 Answers2

1

The database optimizer will execute both variants in the same way. You can use the one that looks better. :)

Andomar
  • 232,371
  • 49
  • 380
  • 404
0

When you put a query in the "In clause" the database need to calc this query first and than based on the result calc the "main query". if you put values in the "In clause" the database dost need to calc additional query.

As concern to join against query in the in clause the database will optimise them to be the same.

Avihai Marchiano
  • 3,837
  • 3
  • 38
  • 55
  • So,you mean to say putting values is good rather than query ? – Hardik Mishra Jul 24 '12 at 12:17
  • From clear performance point of view putting values is faster since its dosnt required to execute additional query, but performance is not the only perspective . if the differnce is 1 ms and 1 ms is negligible for you , so take the clearance approach. – Avihai Marchiano Jul 24 '12 at 12:36
  • Its true when you have fewer values but when values are more than 1k, then SQL itself has limitation on IN clause.So, This is not the solution what I was looking for. – Hardik Mishra Jul 24 '12 at 16:40