>I created two tables with 3 rows, 3 cols. pk, fk, value;
>The second row in table1 has a null fk.
>the third row in table2 has a null fk.
>
>the following code produced a single row:
>
>

>select t1.*, t2.fkRecord2, t2.value2
>	from TestTable1 t1
>		inner join TestTable2 t2 on t1.fkRecord = t2.fkRecord2
>union
>select t3.*, cast("FKRecord" AS bigint) as field1, cast("Value" as varchar(50)) as field2
>	from TestTable1 t3
>		where not exists (
>			select tt4.*, tt5.fkRecord2, tt5.value2
>				from TestTable1 tt4
>					inner join TestTable2 tt5 on tt4.fkRecord = tt5.fkRecord2)
>

>
>
>Whereas, this commad produced 3 rows with all fields joined.
>
>

>select * from testtable1 tt1 left outer join  testtable2 tt2 on tt1.fkRecord = tt2.fkRecord2
>

>
>
>If I add null handling to the above union, I bet it would eventually return the correct record set, but at what cost. What you suggest is purely ridiculous. Left Outer Join was designed to return all rows regardless of null conditions in the match.

I'll play with this tomorrow, it's too late right now. Anyway, I was just trying to say, that you can re-write LEFT JOIN as INNER JOIN and UNION. I didn't suggest to use it, I merely made a statement. I guess my code needs more adjustments, I wrote it from the top of my head.