SQL Server - LEFT JOIN
What is LEFT JOIN (LEFT OUTER JOIN) in SQL Server ?
Left join is called as Left Outer Join.
It is different from Inner Join, In case of Inner Join SQL Server shows the common data from both table.
But in Left Join SQL Server shows all data from left side table and common matching data from right side table.
If matching record not found from right side table then SQL server show NULL value.
Below image will give you brief idea regarding right join. We have two tables and it is all rows from left side table and matching record from right side tables.
Syntax of Left Join
Note : We use the LEFT JOIN keyword to join two or more table and ON keyword is use to add condition. We can add multiple condition using AND keyword.
Consider below two result set of tblStudentDetails table and tblStudentMarks table, having student and student marks details.
Get list of all student with there respective marks.
Example
Output
Note : In above output of Left Join SQL Server show NULL the data of Jyo and Sudarshan because both student not having marks data.
It return all data from left side table and matching data from right side table.
tblStudentDetails called left table because it is available to left side of 'LEFT JOIN' keyword and same for tblStudentMarks called as right table because it is available to right side 'LEFT JOIN' keyword.
LEFT JOIN with combination of INNER JOIN.
Consider below three result set of tblStudentDetails table, tblStudentMarks table and tblStudentContact table.
Select the matching record from tblStudentDetails and tblStudentMarks and all record from tblStudentContact depending on first join.
Example
Output
LEFT JOIN with where condition.
Consider the below result set and select student whose marks greater than 50 and subject in Math and Science .
Example
Output
