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@postgresql.org)
>

--
Darren Ferguson