Hia Svein,

Thats for the clarification. I can see that:

> select a.* from a where
> not exists(select * from b where a.id = b.id)

Is clearer than the outer join, it's just something I've done for
years...!

I think I read it was efficient once... but might not have been a
reference to Interbase or Firebird..

John

--- In firebird-support@yahoogroups.com, "Svein Erling"
<svein.erling.tysvaer@k...> wrote:

> --- In firebird-support@yahoogroups.com, "johnsparrowuk" wrote:
> > Hi Svein,
> >
> > 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.
>
> 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)
>
> > ??? If it's not valid SQL, shouldn't Firebird refuse to accept it?
> > Also, if it's not valid to reference the 'outer' side of an outer
> > join, does that mean you can't have a WHERE clause at all in a

FULL

> > OUTER?!?!?!
>
> The database I normally work with has one million rows or more in

its

> main tables, and I never use FULL OUTER JOIN on them ;o} You may be
> right in that OUTER JOINed tables can be used in the where clause,

I

> might just have misread some advice on this list a while ago

(though

> I'll continue to put my limiting criteria for the left joined

tables

> in my JOIN clause until I experience a case where it causes

problems).

>
> Set