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 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 :

SQL Self Join

Discussion