Duplicates in Oracle Full Join

Question

I read this article about the working of Full Joins.

The article says that a Full Join, which combines the results of Left Join and Right Join, "retains duplicate rows". Therefore, in order to simulate a FULL JOIN, we use UNION ALL instead of UNION.

But, when I perform a FULL JOIN on two tables in Oracle, I do not find duplicates at all. (I believe, Oracle internally uses the 'UNION ALL' operation on left and right joins to perform a FULL JOIN.) The left join and right join contain some common rows, but when I run a full join, those common rows don't appear twice.

Results of Left Join :

SELECT * FROM ORDERS LEFT JOIN CUSTOMER ON ORDERS.CUSTOMERID = CUSTOMER.CUSTOMER_ID;

Results of Right Join :

SELECT * FROM ORDERS RIGHT JOIN CUSTOMER ON ORDERS.CUSTOMERID = CUSTOMER.CUSTOMER_ID;

Results of Full Join :

SELECT * FROM CUSTOMER FULL OUTER JOIN ORDERS ON CUSTOMER.CUSTOMER_ID = ORDERS.CUSTOMERID;

As you can see from the results of FULL JOIN, it "Does not contain duplicate rows" , even though left join and right join have some common rows.

So, why is it believed that a full join contains duplicate rows ? Am I missing something ?

Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) Beware, the accepted answer & almost all the answers are very poor. — philipxy, Jan 16 '21 at 19:13
See [my answer](https://stackoverflow.com/a/46091641/3404097) at the duplicate link. Please read authoritative documentation like a manual in researching before considering asking. When you get results you don't expect, say exactly what you expected & why, with justification referencing authoritative documentation. For code questions give a [mre]. This is not clear, you don't explain "duplicate rows" or the problem. The article you link isn't clear either, it doesn't clearly say how output is a function of input. (Why do you think it does?) (Rhetorical.) — philipxy, Jan 16 '21 at 19:17
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, Jan 16 '21 at 19:18
@philipxy Your answer did clear up my confusion ! Thanks a lot for the link and the suggestions. Sorry if I did not explain my problem clearly. I'm closing this. — AnonymousMe, Jan 16 '21 at 19:24
You have misread the article. It doesn't say that a full outer join retains duplicate rows. The article says that if you want to *simulate* a full outer join with the union of a left join and a right join (or of two left joins with the tables reversed) you must use `UNION` instead of `UNION ALL`. The reason for this is that the two query results you are going to combine can have rows in common (these are all the matches in contrast to the outer joined rows). `UNION ALL` would hence lead to duplicates, which `UNION` does not. — Thorsten Kettner, Jan 16 '21 at 19:28
Consider editing your question to be the best possible--avoid more downvotes, get upvotes, get downvotes removed, leave something better for future readers, improve your writing skills. Poorly received questions count towards asking limits (deleted or not). — philipxy, Jan 16 '21 at 19:30
... And why simulate a full outer join at all? You'd only do this in a DBMS that doesn't support full outer joins (MySQL comes to mind). Oracle does support full outer joins, so there is no need to simulate anything. — Thorsten Kettner, Jan 16 '21 at 19:35
@ThorstenKettner The blog actually says that its UNION method 1 doesn't work. But its UNION ALL method 2 is also wrong. Full join is inner join UNION ALL null-extended unmatched left & right table rows, which is left join UNION ALL null-extended unmatched right table rows. UNION of left & right join can wrongly remove desired duplicate rows. But null-extended right table rows can't always be found merely via WHERE + IS NULL since NULLs could be input. But actually its added "mutex" boolean column in method 3 can be used with UNION ALL to solve that. — philipxy, Jan 16 '21 at 20:11
The "duplicate" rows from UNION ALL in the blog would be every row in the inner join of the tables--UNION ALL of left & right join gives 2 copies of each in its result. However UNION doesn't work because one of the input tables could contain duplicate rows that must be kept. Also WHERE + IS NULL isn't appropriate if NULLs can be input. To properly simulate full join you can add a known non-null column on input to right join to tell what rows were null-extended per whether that column is null on output, then drop the added column, then UNION ALL with left join output. — philipxy, Jan 16 '21 at 20:35
@philipxy: You are right of course. I didn't read the article thoroughly (not even remotely to tell the truth :-). — Thorsten Kettner, Jan 16 '21 at 22:51
@ThorstenKettner Yeah I read as little as possible & I only read the 1st sentence of the intro until your 1st comment. — philipxy, Jan 16 '21 at 22:58

score 1 · Answer 1 · answered Jan 16 '21 at 19:11

I'm really a bit confused by what you mean by "duplicated rows". Joins don't generated "duplicates". What they do is include all combinations of rows with matching join keys.

In your case, customerid = 3 is repeated, so it would seem to be repeated in one or both tables. This is true in all your join queries.

If your data is properly structured, then orders.customerid should always match customers.customerid, unless the former is NULL. In a properly structured database, full join is very, very rarely needed. For instance, assuming that the customerid has a properly declared foreign key relationship, then you would want left join, with customers as the first table:

SELECT *
FROM CUSTOMERS c LEFT JOIN
     ORDERS o
     USING (CUSTOMERID);

With USING, CUSTOMERID only appears once in the result set.

Duplicates in Oracle Full Join

1 Answers1