Re: [GENERAL] using EXISTS instead of IN: how?

[DeJuan Jackson]

Rory Campbell-Lange wrote:

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
 

try:
SELECT name FROM people
 WHERE EXISTS(SELECT 1 FROM states
                   WHERE name ~*'r' AND people.state = states.state)
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]