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