>>>Nope, it is still correct. Every left join is equal to inner join + union for the rest. You can use NOT EXISTS and the same query as at the top or use CTE in SQL Server 2005. There was an interesting discussion in tek-tips forum on a similar problem, but I'm not going to search for it right now.
>>
>>I asked for an example; you posted one that did not prove your assertion. Like to try again?
>
>I found tek-tips discussion in the meantime, which turned out to be completely unrelevant, but anyway http://tek-tips.com/viewthread.cfm?qid=1361636
>
>As for the select with union, from the top of my head (not-tested):
>

>select * from Table1 inner join Table2 on condition
>union
>select Table1.*, cast to create fields from table2
>where not exists (select * from Table1 inner join Table2 on condition)

>
>AFAIK, tt is the same as
>

>select * from Table1 left join Table2 on condition

>
>In other words, every left join can be re-written as inner join + union. Same goes for the right join and for the FULL join we need two unions. That's just a simple fact, same as that every recursive algorithm can be re-written without recursion.


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.