Re: How would I write this query...

Guy Rouillier wrote:
> Jim Fitzgerald wrote:
>
>>Hi -
>>
>>  I have two tables, one of them has names of people and an associated
>>integer ID.  The other table is a list of the people (from the first
>>table) by their ID number that have signed up for a class.  How would
>>I write a query that would list all the people from the first table
>>that do not have any entries in the second table?   Basically, I want
>>a listing of all my people who have not signed up for the class.
>
>
> select *
> from people
> where id not in
> (
> select id
> from class_registration
> )

Wouldn't a NOT EXISTS be faster? After all, the current record can be
disposed of as soon as there's any reference to it from class_registration.

For example:
select *
   from people
  where not exists (
    select 1
      from class_registration
     where id = people.id
  );

It may be faster to use * or a specific column name in the subquery
instead of the constant value 1. EXPLAIN ANALYZE will tell ;)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //