Hi,

> > I didn't know about the restriction on the WHERE clause... I use
> >this structure a lot. Are you sure it's not ok to say:
> >
> > select a.* from a left join b on a.id = b.id where b.id is null
>
> No, I'm not saying I'm sure about that, just that it confuses me and
> that I am curious about its use.

The WHERE clause will be performed on the result set when all FROM items are
processed. So there is nothing wrong with the query used by John. For OUTER
JOINs this effect (using WHERE clause instead of ON clause) will be
different compared to INNER JOINs.

> Maybe it is the equivalent of what I normally write as
>
> select a.* from a where
> not exists(select * from b where a.id = b.id)

Yes, this should be equivalent and probably faster when there are many
duplicates on the person field.

> > ??? If it's not valid SQL, shouldn't Firebird refuse to accept it?

It's valid.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81