It’s not possible to bind a list of variable length to a single bound variable. Similarly, if you were to bind the string $ids you’ll actually end up with: SELECT foo,blar FROM table WHERE id IN (‘123,535,345,567,878’) (Note the quotes around the list of IDs). Creating your own query with the right number of question … Read more
EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN, it will collect all the results from the sub-query before further processing.
This is the same thing as was asked in this question: Can I bind an array to an IN() condition? The answer there was that, for a variable sized list in the in clause, you’ll need to construct the query yourself. However, you can use the quoted, comma-separated list using find_in_set, though for large data … Read more
(Strictly speaking, IN and ANY are Postgres “constructs” or “syntax elements”, rather than “operators”.) Logically, quoting the manual: IN is equivalent to = ANY. But there are two syntax variants of IN and two variants of ANY. Details: How to use ANY instead of IN in a WHERE clause? IN taking a set is equivalent … Read more
The exists keyword can be used in that way, but really it’s intended as a way to avoid counting: –this statement needs to check the entire table select count(*) from [table] where … –this statement is true as soon as one match is found exists ( select * from [table] where … ) This is … Read more
You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), (). Syntax will be like this: select c.* from comments c join ( values (1,1), (3,2), (2,3), (4,4) ) as x (id, ordering) on c.id = x.id order by x.ordering
Of course if you’re lazy like me, you could just do this: Declare @Ids varchar(50) Set @Ids=”,1,2,3,5,4,6,7,98,234,” Select * from sometable where Charindex(‘,’+cast(tableid as varchar(8000))+’,’, @Ids) > 0