LEFT OUTER JOIN in SQL Server

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

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


Syntax for LEFT OUTER JOIN is:

SELECT <A.Column Names >, <B.Column Names>
FROM <Left side Table Name> <A>
LEFT 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.

















Student information along with second language information can be fetched irrespective of whether valid language information exits or not.
This information can be fetched by left joining STUDENT table with LANGUAGES table:


Query:

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


Result Set:



Result Set contains the Student information and their second language also.

In the above result set STUDENT_ID=8 information is also fetched though this student Language Id is 20. Language ID =20 doesn't found in LANGUAGES table.
Even though matching record does not exist in STUDENT and LANGUAGES table for STUDENT_ID=8 it appears in the result set.

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

If matching record exits in right side table, then right side matching values will be returned 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