SQL Full Join

SQL 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 (OUTER) JOIN

Full Join Syntax :

The syntax of the Full Join is −

SELECT column-name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column-name = table2.column-name;



SQL 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 sql 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.