Limitations of the SharePoint join using CAML
Limitation One
In SharePoint 2010, you can join the primary list to a foreign list and include more than one field from the foreign list. However, the limitation is that the included fields from foreign list have to be the following type:
- Calculated (treated as plain text)
- ContentTypeId
- Counter
- Currency
- DateTime
- Guid
- Integer
- Note (one-line only)
- Number
- Text
The above limitation also explains why you cannot include some types of the fields from the remote list when creating a lookup.
Limitation Two
When using CAML query to join SharePoint lists, there can be joins to multiple lists, multiple joins to the same list, and chains of joins. However, the limitations are only inner and left outer joins are permitted and the field in the primary list must be a Lookup type field that looks up to the field in the foreign list.
Limitation Three
The support for writing the JOIN query in CAML is very limited.I have to hand-code the CAML query to join the lists,not fun at all.Although some blogs post mentioned about using LINQ to SharePoint and get the CAML code from there , but I never get it to work.You can check this blog post for this.Let me know if it works for you.
References:
http://msdn.microsoft.com/en-us/library/ee535502.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.joins.aspx
Leave a comment
YBBEST SharePoint Pie 
There seems to be another limitation — though I can’t quite put my finger on it just yet.
The Join operation will throw an error: “The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.” in some cases.
I’ve been testing this to figure out what causes it. There are various posts and docs that indicate that this can be the result of using unsupported operators (which I’m not), but I think it may actually be due to the number of lookup/fields on the source list/table in question.
The SDK docs do add one additional caveat to this: “For example, if a query to table A effectively requires a separate query to Table B for every row of Table A, then the query to table A is not supported”. This seems to be a weird note to me; isn’t this what an inner join is supposed to do?
It would be good to note that regarding limitation number two, the lookup field must be a single value lookup field. There is good evidence that multiple value lookup fields do not work for caml joins.