SQL FULL JOIN

The FULL (OUTRE) JOIN keyword returns matching values from both tables.

Syntax:

SELECT column_name(s) FROM left_table
FULL OUTER JOIN right_table
ON left_table.column_name = right_table.column_name;

FULL (OUTER) JOIN

DEMO TABLES

STUDENTS TABLE



Data

Roll_No Name Marks
1 Dev 35
2 Ayush 45
3 Ram 55
4 Pyush 65
5 Nazim 75

CLG TABLE



Data

Id Phone_no
1 946547846
2 854547845
3 756547841
4 846447842
5 874647841
10 985647841
11 964747841
12 765447841

Example: To perform full (outer) join on both the above tables

SELECT Roll_No, Name FROM students
FULL OUTER JOIN clg
ON students.Roll_No = clg.Roll_No;

The resultant table will be as shown below:



Data

Roll_No Name Marks Id Phone_no
1 Dev 35 1 946547846
2 Ayush 45 2 854547845
3 Ram 55 3 756547841
4 Pyush 65 4 846447842
5 Nazim 75 5 874647841
6 ABC 75 NULL NULL
7 ABC 75 NULL NULL
8 Nazim 75 NULL NULL
NULL NULL NULL 10 985647841
NULL NULL NULL 11 964747841
NULL NULL NULL 12 765447841