Question

Provide an example of Left Outer Join with Exclusions?

Answer 1

This type of join lets you find the data in one table that doesn't exist in another table. It's an alternative to using NOT IN or NOT EXISTS in a WHERE clause like this:

SELECT p.PeopleID, p.Name
  FROM dbo.People p
  WHERE p.PeopleID NOT IN (SELECT n.PeopleID
    FROM dbo.PhoneNumbers n
    WHERE n.PeopleID IS NOT NULL); 

Here's how you can accomplish the same goal using a left outer join with an exclusion:

SELECT p.PeopleID, p.Name
  ,n.PhoneNumberID, n.PeopleID, n.Number
  FROM dbo.People p
LEFT JOIN dbo.PhoneNumbers n
  ON p.PeopleID = n.PeopleID
  WHERE n.PhoneNumberID IS NULL;