PostgreSQL FULL OUTER JOIN

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

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.

Example :

  1. SELECT item.item_no,item_descrip,  
  2. invoice.invoice_no,invoice.sold_qty  
  3. FROM invoice  
  4. FULL JOIN item  
  5. ON invoice.item_no=item.item_no  
  6. ORDER BY item_no;  

OR

  1. SELECT item.item_no,item_descrip,  
  2. invoice.invoice_no,invoice.sold_qty  
  3. FROM invoice  
  4. FULL OUTER JOIN item  
  5. ON invoice.item_no=item.item_no  
  6. ORDER BY item_no;  

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 .

An alternate statement for this example : using UNION clause

  1. SELECT item.item_no,item_descrip,  
  2. invoice.invoice_no,invoice.sold_qty  
  3. FROM invoice  
  4. LEFT JOIN item  
  5. ON invoice.item_no=item.item_no  
  6. UNION  
  7. SELECT item.item_no,item_descrip,  
  8. invoice.invoice_no,invoice.sold_qty  
  9. FROM invoice  
  10. RIGHT JOIN item   
  11. ON invoice.item_no=item.item_no  
  12. ORDER BY item_no;