> As you said, there are only few values not null, so first, i think you

should cut off null rows, then ask for

> join. optimizer "should" be told to make such steps : 1. filter nulls

2. join. (ps. 2.1. t1.a = t2.b is same

> as t2.a =t1.b, because same table) So, your query ( or its variations)

would be 1.

>
>Select
> *
>From
> tbl t1
>Where
>t1.a is not null and t1.b is not null

Actually, there are a lot of nulls. What there isn't, is a lot of
duplicate values once nulls are excluded.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adomas
Urbanavicius
Sent: Tuesday, November 15, 2005 12:52 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Long query help

As you said, there are only few values not null, so first, i think you
should cut off null rows, then ask for join. optimizer "should" be told
to make such steps : 1. filter nulls 2. join. (ps. 2.1. t1.a = t2.b is
same as t2.a =t1.b, because same table) So, your query ( or its
variations) would be 1.

Select
*
From
tbl t1
Where
t1.a is not null and t1.b is not null
and
exists (
select 1
from tbl t2
where (t2.a = t1.b
/////// and t2.a is not null and t1.b is not null) /// this is
not found already as nulls ommited from first condition
/////// or (t2.b = t1.a and t2.b is not null and t1.a is not null)
////// if t2.a = t1.b, so in reverse would be found anyway
)