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.

left join example in sql server

Syntax of Left Join

SELECT < list of columns >
FROM table1 AS t1
LEFT JOIN
table2 AS t2 ON t1.column1Value = t2.Column1Value AND t1.column2Value = t2.Column2Value
WHERE < condition if any >

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.

left join example in sql server

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks
FROM
tblStudentDetails AS sd
LEFT JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId

Output

left join with condition in sql server


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.

left join with inner join condition in sql server

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks, sc.StudentContact
FROM
tblStudentDetails AS sd
INNER JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId
LEFT JOIN
tblStudentContact AS sc ON sd.StudentId = sc.StudentId

Output

The INSERT statement conflicted with the CHECK constraint


LEFT JOIN with where condition.

Consider the below result set and select student whose marks greater than 50 and subject in Math and Science .

left join with where condition

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks, sd.City
FROM
tblStudentDetails AS sd
LEFT JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId
WHERE sd.City IN ('Pune','Delhi')

Output

left join example