Oracle question

chughes

I'm doing an Oracle fundamental course and I have a question that puzzles me. What is the difference between UNION ALL and FULL OUTER JOIN. They look the same to me. Can anyone explain the difference to me ?

none

Check here, they discuss FULL OUTER JOINs but briefly touch on UNION as well.

In a few words, UNION is a "sequential" combination while JOIN is "parallel". When you UNION two SELECTs, you basically say "I need the results of the first select followed by the results of the second select". So UNION combined resultsets sequentially, or added them. On the other hand, what JOINs do is multiply resultsets, or process them simultaneously (in parallel). If you can't remember "sequential" or "parallel" processing, then remember addition (UNION) and multiplication (JOIN).

As the example above shows, sometimes you can use both ways to achieve similar results but don't let it mislead you as their principles are completely different. As with math, 2+2 is the same as 2*2 but the operators don't have much common.

With regard to the specifics of ALL (for UNION) and FULL (for JOIN), what they do is UNION ALL simply keeps duplicates (as UNION by default removes them) while FULL JOIN keep non-matching rows on both sides (whereas simple INNER JOIN doesn't preserve non-matching rows at all and LEFT JOIN or RIGHT JOIN keep them on one side only).

chughes

Thanks None and sorry for the delay in thanking you.