PostgreSQL Full Join

All the matched and unmatched records are retrieved from the both the tables present in the query.Full Join is used to return all data or records when a match occurs in a Table 1 or Table 2 record and NULLs have to be filled in for missing matches on either side. It is also known as Full (OUTER) JOIN.

Full Joins Syntax :

The syntax of the Full Joins statement is −

SELECT col_name,...
FROM table_name1 t1  [FULL] JOIN table_name2 t2
ON t1.col_name = t2.col_name



PostgreSQL Full Join Example :

Consider the Customer table with the following records -

ID Name Age Address Salary
1 Aarav 36 Udaipur 35000
2 Vivaan 33 Mumbai 30000
3 Reyansh 28 Chennai 40000
4 Muhammad 29 Udaipur 50000
5 Sai 27 Mumbai 27000


Consider the Order table with the following records -

OID Date CustomerID Amount
102 2019-10-08 00:00:00 3 3000
100 2019-10-08 00:00:00 3 1500
101 2019-11-20 00:00:00 2 1800
103 2018-05-20 00:00:00 4 2500


TEST CASE 1 :-Let us join these two tables using the Full JOIN as follows

SELECT  ID, Name, Amount, Date
FROM Customer
FULL JOIN Order
ON Customer.ID = Order.Customer;


The result for the respective PostgreSQL query is as follows −

ID Name Amount Date
1 Aarav NULL NULL
2 Vivaan 1800 2019-11-20 00:00:00
3 Reyansh 3000 2019-10-08 00:00:00
3 Reyansh 1500 2019-10-08 00:00:00
4 Muhammad 2500 2018-05-20 00:00:00
5 Sai NULL NULL
3 Reyansh 3000 2019-10-08 00:00:00
3 Reyansh 1500 2019-10-08 00:00:00
2 Vivaan 1800 2019-11-20 00:00:00
4 Muhammad 2500 2018-05-20 00:00:00




Visit :


Discussion



* You must be logged in to add comment.