using EXISTS instead of IN: how?

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: using EXISTS instead of IN: how?
Date: 2003-07-22 16:28:45
Message-ID: 20030722162845.GA2623@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have been informed that at present (postgres 7.3.2) using IN is not
advised, and I should replace it with EXISTS. I can't seem to get it to
work.

I've tried replacing (example):

SELECT
name
FROM
people
WHERE
state IN (
SELECT
id
FROM
states
WHERE
name ~* 'r'
);

with

SELECT
name
FROM
people
WHERE
exists (
SELECT
1
FROM
states
WHERE
name ~* 'r'
);

However the second example simply finds all records in people.

Thanks for any help,
Rory

--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Csaba Nagy 2003-07-22 16:36:10 Re: using EXISTS instead of IN: how?
Previous Message Patrick Welche 2003-07-22 16:21:58 Re: Having a 2-column uniqueness constraint