+2 votes
in Sql by
Provide an example of Left Outer Join with Exclusions?

1 Answer

0 votes
by

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; 

Related questions

+2 votes
+1 vote
asked Jan 14, 2022 in Sql by GeorgeBell
0 votes
+2 votes
asked Jan 15, 2022 in Sql by GeorgeBell
...