SQL & PL/SQL :: ANSI Join Returning Wrong Dataset?

...continued from "Problems with full outer join"I think that ANSI joins in Oracle doesn't work correctly. Or am I doing something wrong? The query looks like this:

select nvl(k.id_pers,t.id_pers) id_pers, k.dat_avst
, id_trans, dat_trans
from
( select ka.id_pers, ka.dat_avst, ka.dat_nasta_avst

[code]...

It's a full outer join between one table (with a subquery) and an inline view with two tables You can see the returned rows in the listing below. The query returns one row where there is a match between t_trans and t_kontoavst.It also returns two rows from table t_kontoavst with no correspondence in t_trans.Finally it returns 26 rows from the table t_trans with no correspondence in t_kontoavst. But among them there are many rows in contradiction to the conditions:

and trunc(dat_trans) >= to_date('20040101','yyyymmdd')
and trunc(dat_trans) <= to_date('20050604','yyyymmdd')

Actually it seems to return all the 27 rows in t_trans (one of them joined to t_kontoavst).These conditions are actually not part of the join so I changed it to:

where trunc(dat_trans) >= to_date('20040101','yyyymmdd')
and trunc(dat_trans) <= to_date('20050604','yyyymmdd')

It's not clear to me if this where condition belongs to the joined result or just to the right joined inline view.With this change the correct rows from t_trans where returned but unfortunately the two rows from t_kontoavst with no correspondence in t_transdisappeared. I thought maybe that is because dat_trans is null for these two rows after the join. Therfore I also includeddat_trans to be null. This can only happen when t_trans is missing

where dat_trans is null
or (trunc(dat_trans) >= to_date('20040101','yyyymmdd')
and trunc(dat_trans) <= to_date('20050604','yyyymmdd'))

But that didn't change the result I have also tried right and left joins and it produces similar errors.One other thing i tried was to replace the inline view with one of the underlying tables t_trans. But the result was the same.Data returned from the original query (see query above):

ID_PERSDAT_AVSTID_TRANSDAT_TRANS
1945050505022005-05-011721642005-05-16
194505050502null1723722005-06-16
194505050502null1723732005-07-16

[code]...

Data in the tables.SQL Statement which produced this data:

select id_trans, id_pers, dat_trans
from t_trans
order by id_pers, dat_trans, id_trans

ID_TRANSID_PERSDAT_TRANS
1721641945050505022005-05-16
1723721945050505022005-06-16
1723731945050505022005-07-16

[code]...

SQL Statement which produced this data:

select id_pers, dat_avst
from t_kontoavst
order by id_pers, dat_avst

ID_PERSDAT_AVST
1945050505021997-05-01
1945050505022005-05-01
1958080808071997-05-01

[code]...