SQL: Full Outer Join

A full outer join is used when you need to return all rows from both the left and right tables.  Where there is no row match in the other table then a row of nulls is returned.

For example:


SELECT E.LastName, L.LocationID
FROM Employees E Full OUTER JOIN Location L
ON E.LocationID = L.LocationID

This should include, in a single list, employees that have not been allocated a location and those locations that do not have any employees.  The list can be filtered futher by checking for NULLs on either table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s