Having worked on CRM for ten years, I thought I understood everything that was possible with FetchXML. After all it seems pretty straight forward and each clause has almost a one to one equivalent with SQL. However, while I was recently doing some work on a project that required me to find records missing certain child records, I was surprised to find my understanding of left outer joins was incomplete.
In the project I was working on, we were using the native Connection entity to track relationships between contacts and users. Some of the Connections were manually created, but others needed to be automatically created based on business logic. In some cases we needed to detect all contacts that a user did not have a connection of a specified role with. This seemed like a good case for using a left outer join and I sat down and wrote the following FetchXML:
As I reviewed the FetchXML, I became concerned that I wouldn’t get the proper results with this query. I was assuming that CRM only used the to and from attributes on the link-entity element to build the on clause for the left join in SQL. I knew that if the additional conditions inside the link-entity were added to the SQL where clause, that I would get no rows back. In short, I was worried the generated SQL would look something like this:
It was enough of a concern that I decided to fire up SQL Profiler on my local dev org and see what exactly CRM generates in this case. Much to my surprise I found the following (slightly cleaned up to help legibility):
So in the end, CRM came through and put the link-entity’s conditions in the on clause for the left join. This subtle change makes a huge difference in the results returned and makes left joins much more useful in CRM than one might assume based on the FetchXML structure. This left me with an efficient query to solve the business requirement and a new found respect for FetchXML.