How to do a full outer join without having full outer join available

Question

Last week I was surprised to find out that sybase 12 doesn't support full outer joins. But it occurred to me that a full outer join should be the same as a left outer join unioned with a right outer join of the same sql. Can anybody think of a reason this would not hold true?

Answer 1

UNION ALL the left join with the right join, but limit the right join to only rows that do not exist in the base table (return null on the join when they would not be null in the table if they existed).

For this code you will need to create two tables t1 and t2. t1 should have one column named c1 with five rows containing the values 1-5. t2 should also have a c1 column with five rows containing the values 2-6.

Full Outer Join:

select * from t1 full outer join t2 on t1.c1=t2.c1 order by 1, 2;

Full Outer Join Equivalent:

select t1.c1, t2.c1 from t1 left join  t2 on t1.c1=t2.c1
union all
select t1.c1, t2.c1 from t1 right join t2 on t1.c1=t2.c1 
where t1.c1 is null
order by 1, 2;

Note the where clause on the right joined select that limits the results to only those that would not be duplicates.