What Join is it called and how to do it in Oracle SQL-CodePudding

Please have a look at these Tables below. I've three tables where Invoice and Receipt tables don't have any direct link. They can only be linked using another Table and here it is "Invoice * Receipt Link Table"

Invoice Table

Invoice No
1
2

Receipt Table

Receipt No
1
2

Invoice * Receipt Link Table

Invoice No	Receipt No
2	1

Result table I'm expecting

Invoice No	Receipt No
1	null
2	1
null	2

What kind of join do we even call it and how to make a join among these three tables to get result as in the 4th table?

CodePudding user response：

Use a FULL OUTER JOIN:

SELECT i.invoice_no,
       r.receipt_no
FROM   invoice i
       FULL OUTER JOIN link l
       ON (i.invoice_no = l.invoice_no)
       FULL OUTER JOIN receipt r
       ON (r.receipt_no = l.receipt_no)

Which, for the sample data:

CREATE TABLE invoice (Invoice_No) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL;

CREATE TABLE receipt (receipt_No) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL;

CREATE TABLE link (invoice_no, receipt_No) AS
SELECT 2, 1 FROM DUAL;

Outputs:

INVOICE_NO RECEIPT_NO

2 1

null 2

1 null

db<>fiddle here

CodePudding user response：

This is called a full outer join.

One way to express it:

select * 
from invoice 
full outer join receipt 
     on (invoice_id, receipt_id) in (
         select invoice_id, receipt_id from invoice_receipt
     )