I am trying to join two tables (check and invoice totals) on a foreign key (job id). I don't really need anything from the parent so I am not including it in the query. however, there are checks and invoices that are not part of a Job, and there for the Job ID is null. My attempts at joining them led me to the sample below that does not seem to behave as I would expect, Can someone tell me if this is a bug, or is there a better way to solve it than the final query involving a union?

Carl

*

create database foobar
create table foo ( fkey i null, ffid1 c(1) null )
insert into foo values( 1, "a" )
insert into foo values( 2, "b" )
insert into foo values( 3, "c" )

create table bar ( bkey i null, bfid1 c(1) null )
insert into bar values( 1, "b" )
insert into bar values( 2, "c" )
insert into bar values( 3, "d" )

select * ;
 from foo inner join bar on ffid1 = bfid1 ;
 to screen

*     FKEY FFID1    BKEY BFID1
*        2 b           1 b
*        3 c           2 c
*

select * ;
 from foo left join bar on ffid1 = bfid1 ;
 to screen
*
*     FKEY FFID1    BKEY BFID1
*        1 a      .NULL. .NULL.
*        2 b           1 b
*        3 c           2 c
*

select * ;
 from foo right join bar on ffid1 = bfid1 ;
 to screen

*
*     FKEY FFID1    BKEY BFID1
*        2 b           1 b
*        3 c           2 c
*   ,NULL. .NULL.      3 d
*

select * ;
 from foo outer join bar on ffid1 = bfid1 ;
 to screen

*
*     FKEY FFID1    BKEY BFID1
*        2 b           1 b
*        3 c           2 c
*
select * ;
 from foo left join bar on ffid1 = bfid1 ;
union select * ;
  from foo right join bar on ffid1 = bfid1 ;
  to screen

*
*     FKEY FFID1    BKEY BFID1
*   .NULL. .NULL.      3 d
*        1 a      .NULL. .NULL.
*        2 b           1 b
*        3 c           2 c


*