No, in these variants are same: You can see – the execution plans are same too: postgres=# explain select * from foo1 where id in (select id from foo2); ┌──────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞══════════════════════════════════════════════════════════════════╡ │ Hash Semi Join (cost=3.25..21.99 rows=100 width=4) │ │ Hash Cond: (foo1.id = foo2.id) │ │ -> Seq Scan on … Read more
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
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
So as I was preparing to submit this, I double-checked a bunch of the stuff I had looked up previously and found the thing I had somehow missed and would have saved this question from being necessary. As it turns out, both of these options: Passing the argument as a List<String> Passing the argument as … 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.
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
(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