--- In firebird-support@yahoogroups.com, "Woody" <woody-tmw@...> wrote:

>
> From: "Ann W. Harrison" <aharrison@...>
> >
> > A slightly more conventional version would be
> >
> > select 1
> > from rdb$database
> > where exists (select 1
> > from <table>
> > where <field> is NULL)
> >
> >
> > Either is exactly a full table scan if the field is never null, but
> > less than that if the field can be null.
>
> Ann,
>
> In what way is
>
> select 1
> from rdb$database
> where exists (select 1
> from <table>
> where <field> is NULL)
>
> any better than just
>
> select 1 from <table> where <field> is NULL
>

Careful Woody. This was not his query. He used the FIRST directive
which is equivalent to Ann's query. The main difference I can see is
that Ann's query will need to do a natural scan of RDB$DATABASE as
well, but given it is a single record it will not be measurable.

If the release notes are correct, then the First syntax was introduced
in FB 1.0, so perhaps it may be better to use Ann's syntax if
maintaining earlier versions of IB.

But if I was using the RDB$DATABASE syntax for the purpose of cross DB
compatibility, then I would consider writing a view ONLYONERECORD, and
in Firebird define that view as

select 1
from RDB$DATABASE

In other DBMS, you define it as whatever query needs to be run to
return the value 1. Then you do not have to search for all instances
where you have used FIRST or RDB$DATABASE.

Adam