JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.
FULL OUTER JOIN or FULL JOIN is used to retrieve complete data from left side table as well as complete data from right side table.
That is whether matching record is found or not in the tables all the records from Left and Right side tables will be retrieved.
Syntax for FULL OUTER JOIN is:
SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A>
FULL OUTER JOIN <Right side Table Name > <B>
ON <A.matching column Name> = <B.Matching Column Name>
In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.
Example:
Consider 2 tables STUDENT and LANGUAGES .
STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES table holds all the languages that are offered in that school.
SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.
Below screen shot represents the data in STUDENT and LANGUAGES table.
FULL OUTER JOIN or FULL JOIN is used to retrieve complete data from left side table as well as complete data from right side table.
That is whether matching record is found or not in the tables all the records from Left and Right side tables will be retrieved.
Syntax for FULL OUTER JOIN is:
SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A>
FULL OUTER JOIN <Right side Table Name > <B>
ON <A.matching column Name> = <B.Matching Column Name>
In the above syntax A indicates the alias name for left side table Name
B indicates the alias name for right side table Name.
Example:
Consider 2 tables STUDENT and LANGUAGES .
STUDENT table holds STUDENT details like student id,name, class and student second language. LANGUAGES table holds all the languages that are offered in that school.
SECOND_LANG_ID in STUDENT will hold the LANG_ID value of LANGUAGES table.
Below screen shot represents the data in STUDENT and LANGUAGES table.
If all the information needs to be fetched from both LANGUAGES and STUDENT table, then following query can be used:
Query:
SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
FULL OUTER JOIN LANGUAGES L
ON S.SECOND_LANG_ID =L.LANG_ID
Result Set:
Result Set:
If matching record exits in the tables then matching values will be returned else Nulls will be returned for the Non matching records.
FULL OUTER JOIN result set will contain complete data from both the tables which are listed in join clause.
