On Wed, 07 May 2008 22:20:28 +0200, cvh@LE
<christian.hansel@cpi-service.com> wrote:
> On May 7, 6:16*pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Wed, 07 May 2008 18:11:48 +0200, GregoryD <deergrego...@gmail.com> *
>> wrote:
>> > I am trying to select all the records in users whose ids do not exist
>> in
>> > opt. *The ids in both tables are unsigned INTEGER(10).

>> > I have tried the following (not so much the right query as trying to *
>> > figure
>> > out why none of these queries work on the server):
>>
>> > SELECT * FROM users WHERE id <> ANY (SELECT id FROM opt);
>>
>> > SELECT * FROM users WHERE id IN (SELECT id FROM opt);
>>
>> > SELECT * FROM users WHERE users.id IN (SELECT id FROM opt);
>>
>> > In each case, I get a syntax error. *What exactly am I doing wrong?
>>
>> None of those give me a syntax error, and these two work if you just ad
>> *
>> NOT:
>>
>> SELECT * FROM users WHERE id NOT IN (SELECT id FROM opt);
>> SELECT * FROM users WHERE users.id NOT IN (SELECT id FROM opt);
>
> While Rik's solution is valid and does work I would rather opt for a
> solution based on joins since these usually are more efficient than
> subselects
>
> Select u.* FROM users u left join opt o using(id) where isnull(o.id)

I second that, but I detest USING statements. Being more explicit (LEFT
JOIN opt o ON o.id = u.id) would have my preference.
--
Rik Wasmus