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(at)magproductions(dot)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 //