Welcome to Lesson 10 of the TSQL Tutorial. In this lesson we will cover left outer joins. I hope you enjoy the lesson.

Select Statements

LEFT OUTER JOIN

 

LEFT OUTER JOIN (definition): All rows from the first-named table (the "left" table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the
right table do not appear.

An OUTER JOIN is a join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:

A LEFT JOIN is the same as a LEFT OUTER JOIN depending on the database.

LEFT OUTER JOIN (usage)

SELECT field1, field2, field3
FROM table1 LEFT OUTER JOIN table2
        ON table1.field= table2.field

Example: This is basically the same query we used for the INNER JOIN example.
            

SELECT e.LastName, e.FirstName, o.OrderID
FROM Employees e LEFT OUTER JOIN Orders o
        ON e.EmployeeID = o.EmployeeID

 

Result: Notice that the LEFT OUTER JOIN vs the INNER JOIN

LEFT OUTER JOIN				INNER JOIN
LastName	FirstName	OrderID
Davolio	Nancy
Fuller	Andrew
Callahan	Laura
Smith	John			LastName	FirstName	OrderID
Buchanan	Steven	10248		Buchanan	Steven	10248
Suyama	Michael	10249		Suyama	Michael	10249
Peacock	Margaret	10250		Peacock	Margaret	10250
Leverling	Janet	10251		Leverling	Janet	10251
Peacock	Margaret	10252		Peacock	Margaret	10252
Leverling	Janet	10253		Leverling	Janet	10253
Buchanan	Steven	10254		Buchanan	Steven	10254
Dodsworth	Anne	10255		Dodsworth	Anne	10255
Leverling	Janet	10256		Leverling	Janet	10256
Peacock	Margaret	10257		Peacock	Margaret	10257

 

The LEFT OUTER JOIN returned results for ALL employees, even one without orders.