Re: IN vs EXIIST

On Thu, 19 Sep 2002, Jean-Christian Imbeault wrote:

> I find myself writing a lot of queries with this pattern:
>
> select distinct key1 from A where id not it
> (select distinct key1 from A where x='false');
>
> The reason being that key1 is not a primary key (key1, key2 is the
> primary key). i.e. I have a table like this
>
> key1 key2 x
> ------------------
> a 1 t
> a 2 t
> a 3 f
> b 1 t
> b 2 t
> b 3 t
> c 3 t
> c 4 f
>
> So basically I want key1 values for which all the X's are true.

Why areyou using the sub select. If you just want all the key1 where x is
true then the following will work

SELECT DISTINCT(key1) FROM a WHERE x = TRUE;

If you want all rows and don't care about duplicates then remove the
distinct.

Hope this helps

>
> I've seen many posts saying that using IN is not optimal and replacing
> it with EXISTS is much better. I've read the only docs but I can't
> understand the difference between the two or how to convert.
>
> Can someone point me to some other docs or explain to me how to convert?
> Or is my table schema wrong?
>
> Thanks!
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Darren Ferguson