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

Select Statements

RIGHT OUTER JOIN

 

RIGHT OUTER JOIN (definition): All rows in the second-named table (the "right" table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included.

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 RIGHT JOIN is the same as a RIGHT OUTER JOIN depending on the database.

 

RIGHT OUTER JOIN (usage)

SELECT field1, field2, field3
FROM table1 RIGHT 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 RIGHT OUTER JOIN Orders o
        ON e.EmployeeID = o.EmployeeID

 

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

RIGHT OUTER JOIN				INNER JOIN
LastName	FirstName	OrderID
Buchanan	Steven	10248		LastName	FirstName	OrderID
Suyama	Michael	10249		Buchanan	Steven	10248
Peacock	Margaret	10250		Suyama	Michael	10249
Leverling	Janet	10251		Peacock	Margaret	10250
Peacock	Margaret	10252		Leverling	Janet	10251
Leverling	Janet	10253		Peacock	Margaret	10252
Buchanan	Steven	10254		Leverling	Janet	10253
Dodsworth	Anne	10255		Buchanan	Steven	10254
Leverling	Janet	10256		Dodsworth	Anne	10255
Peacock	Margaret	10257		Leverling	Janet	10256
		10258		Peacock	Margaret	10257

The Right OUTER JOIN returned results for OrderID 10258 even though it did not have a corresponding employee.

The RIGHT and LEFT Joins are basically the same, the only thing matters is the order of the tables.