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
[EMAIL PROTECTED]
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 //
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
