PostgreSQL FULL OUTER JOIN

What is Full Outer Join in PostgreSQL?

PostgreSQL FULL OUTER JOIN returns all rows from both the participating tables, extended with nulls if they do not have a match on the opposite table. The FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

Syntax:

SELECT * | column_name(s)
FROM table_name1
FULL [OUTER] JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

Pictorial Presentation of PostgreSQL Full Outer Join

PostgreSQL FULL OUTER JOIN fetches the matching rows from the tables ( table1 and table2 ) participating in this join and also the rows with the null if they do not have a match on the opposite table.

Sample table: Customer

Sample table: Item

Sample table: Invoice

Example:

Code:

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM invoice
FULL JOIN item
ON invoice.item_no=item.item_no
ORDER BY item_no;

OR

Code:

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM invoice
FULL OUTER JOIN item
ON invoice.item_no=item.item_no
ORDER BY item_no;

Output:

Explanation

In the above example, the matching rows from both the table item and invoice have appeared, as well as the unmatched row i.e. I8 of item table which is not exists in the invoice table have also appeared, and for this row of item table a new row in invoice table have generated and sets the value NULL .

Pictorial Presentation for the above example

An alternate statement for this example: using UNION clause

Code:

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM invoice
LEFT JOIN item
ON invoice.item_no=item.item_no
UNION
SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM invoice
RIGHT JOIN item 
ON invoice.item_no=item.item_no
ORDER BY item_no;

Previous: RIGHT JOIN
Next: SUBQUERIES