SQL IN statement or exists

[programmervb netc]

Could I write this as a exists statement we use this quite often and I read it has very poor performance.

WHERE `cl_client_common`.`UniqueNumber-900` IN(5337, 4439, 4725, 4096, 8078, 5912, etc, etc)

We do not know what the values will be until the user selects the clients they want. All of the examples I see are using a subquery for the EXISTS statement and the only way I can think to write that with a subquery would be to use an IN in the subquery. Thank you any help is appreciated.

Humble Programmer

[_Damian S_]

If you are storing the list of numbers in a temp table or similar, you could use

WHERE `cl_client_common`.`UniqueNumber-900` IN (select NUMBER from TEMPTABLE)

or simply by doing an inner join to the temp table... Otherwise, the way you are doing it will have to do!!

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

[Mycroft Holmes]

Learn to use the sql profiler, it will take you query and give you an execution plan, it may even suggest what fields need indexes to optomise your query, it will certinaly give you the cost of each process. You can then decide on the best approach. As DM said you have lots of options, temp table, table variable, sub query. Use and in() or an inner join or even exists as suggested earlier. I'm not sure if the exists is a better solution, I would be tempted to go with the table variable on a large table os just a sub query as you have done. Depends on the size of the data sets and the indexing.

Never underestimate the power of human stupidity RAH

[PIEBALDconsult]

I generally prefer to use a join. I caution against IN unless the values are hard-coded (which is likely a bad design in its own right). I especially dislike subqueries in WHERE clauses. I used to have to use them with Oracle, but I don't recall ever doing them with SQL Server.

[programmervb netc]

We are joining the tables. The values are not really hard coded... The user is presented with a grid with checkboxes they can choose one or as many "clients" as they want in the grid. Then we use the primary key for the record on the grid to create the new sql statement. So user selects primary key 1,2,3,4 then our IN statement will be `UniqueNumber IN (1,2,3,4)

Humble Programmer