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.

Note
  • There are requirements to keep in mind when creating list joins. You cannot join just any two lists, regardless of type. And if two lists can be joined, not just any primary and foreign field can be used as the “join on” pair of fields. The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list. For this reason, all joins mirror existing lookup relations between lists.
  • If you wish to include fields of your lookup list in your query clause or viewfields expression. Only the following types of fields can be included in a ProjectedFields element:
    • Calculated (treated as plain text)

    • ContentTypeId

    • Counter

    • Currency

    • DateTime

    • Guid

    • Integer

    • Note (one-line only)

    • Number

    • Text

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. 

Key Terms
  • Parent List - SharePoint list on which we will be calling the GetItems() method. As earlier discussed in the Note above, this list should be having a lookup column to a foreign list or other list..
  • Other List - Other referenced list

 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.

ProjectedFields

The ProjectedFields element creates fields from the foreign lists so that they can be used in the list view.