How many items can be put in "in ( ...)"? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How many items can be put in "in ( ...)"?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-02-27 : 16:26:21

I used "in (...)" statement. How many items can be put in "in ( ...)"?

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-02-27 : 16:35:42

Are you dynamically building your IN statment, or is there a select statement inside of your ()'s?

If you a dynamically building it, you've got 8000 characters, unless you are doing the thing that allows you to contatinate several VARCHAR(8000)'s together.

If you are using a select statement, I'm not aware of any limitation there. The only limitation you'll run into is performance.
IN statements tend to perform poorly. If you change your query to a JOIN type query, it should perform much better, esp with a large IN list.

Ex:
Select mt.Field1, mt2. Field2
FROM MyTable mt
WHERE mt.FK IN (
SELECT PK FROM MyTable2 Where Field = @Field
)

VS

Select mt.Field1, mt2. Field2
FROM MyTable mt
INNER JOIN MyTable2 mt2 ON mt2.PK = mt1.FK

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-02-27 : 16:44:47

My statement like bellow:

select * from Order where OrderID in (select OrderID from OrderHistory
where OrderBy = 'John' and OrderCity = 'New York')

How to join these two tables? How to deal with where clause?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-27 : 16:52:18

Using an INNER JOIN is a basic fundamental of SQL. You really might need to check out a good book on beginning SQL server and work on the basics. BOL has some info and examples, and there are lots of articles and tutorials on the web as well.

Basically,

SELECT
FROM
Table1
INNER JOIN
Table2
ON ..some expression here ...

will "join" two tables together on the expression you give. Typically, something like

INNER JOIN Table2 ON Table1.SomeID = Table2.SomeID

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-02-27 : 17:04:19

Like so:


SELECT o.Field1, o.Field2, oh.Field1, oh.Field2
FROM Order o
    INNER JOIN OrderHistory oh ON oh.OrderID = o.OrderID
WHERE oh.OrderBy = 'John' 
     AND oh.OrderCity = 'New York'

That will be MUCH faster than the query you gave us, esp. once you get a lot of data in that table.

Be sure that you have an Index on OrderID in the OrderHistory table. That will speed things up greatly as well.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 17:47:36

Might cause duplicates though [unless DISTINCT used] ... and "select *" is going to give a different column list <vbg>

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources