New Left Outer Join feature in FetchXML

Question

I understand that Left Outer Join basically means that whether or not the condition is satisfied in the target table, get all the rows in the first table.

"Microsoft Dynamics CRM 2013 Unleashed" book, explains that CRM 2013 introduces a new feature which is Left Outer Join in FetchXML. Then provides the example below of a FetchXML that it states will result in showing all contacts WITHOUT ANY Opportunities :

I'm unable to test this example, but the stated result seems to me like an Inner Join not a Left Outer Join. For if it's a Left Outer Join then it should result in showing all Contacts whether or not they have no opportunities. Am I missing something here ?

Answer 1

To find contacts without opportunities, you must first identify all contacts with opportunities, and then exclude those from the set of all opportunities.

An SQL JOIN matches up the records in the two tables, so an INNER JOIN will tell you which contacts have opportunities (and what those opportunities are). A LEFT OUTER JOIN does the same match, but leaves the unmatched rows in the result set in addition to the matches.

By filtering out the rows from the joined set which have matched an opportunity, you can see which contacts have no opportunity. Since the unmatched part of an OUTER JOIN in SQL will have NULL for all columns of the unmatched table, you can do this by testing a column which would never otherwise be NULL, such as the Primary Key.

The SQL equivalent to the XML in the question is something like this:

SELECT contact.fullname
FROM contact
LEFT JOIN opportunity ON opportunity.contactid = contact.contactid
WHERE opportunity.opportunityid IS NULL