SharePoint 2010 + supports Joins in the SPQuery for over more than 4 year. But would like to take this blog on it to clarify the concepts the way I have understand the things.
Foremost to create a join, We should make sure that we are not stuck because of any of the restriction mentioned in below note.
SPQuery.Joins and SPQuery.ProjectedFields, together these two properties when used in conjugation Joins the two or more SharePoint list.
We will look at each of these in more detail when we will discuss and understand the syntax. But definition is as below
Joins Clause :
The Joins element contains one or more Join elements. There are two types of possible Joins inner join or left outer join between two lists.
At least one of these joins must be from the parent list of the view, called the primary list, and some other list, called the foreign list. But there can be additional joins from that foreign list to another foreign list, and so on.
The ProjectedFields element creates fields from the foreign lists so that they can be used in the list view.