Office Training

IT Training

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

Select Statements

FULL OUTER JOIN

FULL OUTER JOIN (definition): All rows in all joined tables are included, whether they
are matched or not.

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

FULL OUTER JOIN (usage)

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

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

FULL OUTER JOIN			INNER JOIN


Davolio	Nancy
Fuller	Andrew
Callahan	Laura
Smith	John
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 FULL OUTER JOIN returned unmatched results for both tables.

TrainingByEmail.com

Office Training

IT Training

Select Statements