RIGHT OUTER JOIN in SQL SERVER

JOIN is the keyword or Command which is used to link 2 or more tables to retrieve data.

RIGHT OUTER JOIN or RIGHT JOIN is used to retrieve matching data from left side table and complete data from right side table.

 
Syntax for RIGHT OUTER JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A>
RIGHT 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 languages information needs to be retrieved along with the students information following query can be used.

Query:
SELECT S.STUDENT_ID,S.NAME,S.CLASS,L.NAME
FROM STUDENT S
RIGHT OUTER JOIN LANGUAGES L
ON S.SECOND_LANG_ID =L.LANG_ID

Result Set:

















Result Set contains complete information from LANGUAGES (right side table) table and matching information from STUDENT (Left side table) table .

Even though no matching record found for the languages like Tamil, Punjab,Telugu etc., still they are displayed because these records are part of Right Side table.


That is RIGHT JOIN will return all the records from the right side table irrespective of matching record in left side table.

If matching record exits in left side table, then left side matching values will be returned in the result set, else NULLs will be returned.



To know more about other types of JOINs in SQL SERVER refer the below link:

JOIN in SQL SERVER

This entry was posted in , . Bookmark the permalink.

Leave a reply