Limitations with FetchXML

1.      RIGHT OUTER JOIN is not supported.

2.      You can't compare two fields directly. For instance, you won't be able to find an      equivalent query for the following SQL script:

SELECT * FROM account WHERE telephone1 <> telephone2

SELECT a.* FRM entity1 a INNER JOIN entity2 b ON a.entity1id = b.entity1id WHERE a.name = '123' OR b.name = '123'

3.      The right side of the comparison has to be a constant value.

4.      You can't have OR condition across entities. For instance, the following SQL query is not supported by FetchXML.

5.      If you ever have to do so, you would have to break the query into two, and get the results by issuing two queries against CRM server separately, then merge the result set.

6.      You can't use SQL functions in FetchXML query. CRM has support for some built-in functions, but any additional SQL functions are not supported.

7.      No support of UNIONs (Thanks to Dirk Fabricius for his contribution by commenting below). 

8.      There is no support of a CASE / WHEN structure.

9.      When you issue a FetchXML query, the maximum number of records you get back from CRM server is 5,000 each time. If you want to get more records from CRM server, you would have to use paging cookie.

10.    You can't have more than 10 linked entities in a FetchXML query. It is possible to overcome this limit by creating or updating a QueryLinkEntityLimit setting, however this is generally not recommended. If you ever run into this situation, you would definitely want to re-visit your CRM data model or re-engineer your query.

11.    When you perform an aggregation, the maximum number that will participate in the aggregation will be 50,000 records. For instance, if you do a COUNT aggregation, the maximum value you can get back from CRM is 50,000 even though that you might have more records in the system. This is a by-design behavior which is for performance reason. This can be overcome by updating "AggregateQueryRecordLimit" setting, however it is generally not recommended.

12.    There is no way to use subquery.