How to make a full outer join without having a full outer join

Question

Last week, I was surprised to learn that sybase 12 does not support a full external connection. But it occurred to me that the full outer join should be the same as the left outer join combined with the right outer join of the same sql. Can anyone think of a reason that would not be confirmed?

Answer 1

UNION-the task of two operators OUTER JOINshould lead to duplication of lines representing the data that you received from INNER JOIN. You probably need to do SELECT DISTINCTin the dataset being created UNION. Generally, if you need to use SELECT DISTINCTthis, it means that this is not a well-designed request (or so I heard).

Answer 2

If you combine them with UNION ALL, you will get duplicates. If you just use UNIONwithout ALL, it will filter duplicates and therefore will be equivalent to a full join, but the query will also be much more expensive because it has to perform a separate view.

Answer 3

UNION ALL , , ( null , , ).

t1 t2. t1 c1 , 1-5. t2 c1 , 2-6.

:

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

:

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;

where , , .

Answer 4

• , -, , 12.x. EndOfLifed 31 2009 , , 3 2007 . 15.0.2 ( ) 2009 . 15.5 02 2009 , 13 , .

• ASE 12.5.4 . ( , 12.5.0.3, ).

• DB2 Sybase FULL OUTER JOIN, , : LEFT ... UNION ... RIGHT ALL. " " FOJ; .

• , Sybase DB2 , FOJ, ..

• , SQL, Sybase, FOJ, 12.x; 15.x. RANK(): , .

• , FULL OUTER, , , , . . LEFT RIGHT .

• Depending on your inconsistencies SARG and DataType etc. he still has to sort-merge, but it also translates at all three levels: disk I / O subsystem; engine (s); and network handler. If your tables are separated, then they are additionally parallelized at this level.

• If your server is not configured and your result set is very large, you may need to increase proc cache sizeand number of sort buffers. It's all.