Join operation in sharepoint 2010

In this article I am showing how can we perform inner join between 2 different sharepoint lists.


class Program

{static void Main(string[] args){

using (SPSite site = new SPSite("URL")){

using (SPWeb web = site.OpenWeb()){

SPQuery query = new SPQuery();
query.Joins =
"<Join Type='INNER' ListAlias='Employee1'>" +
"<Eq>" +
"<FieldRef Name='Manager' RefType='ID'/>" +
"<FieldRef List='Employee1' Name='ID'/>" +
"</Eq>" +
"</Join>";　
query.ProjectedFields = "<Field Name='Employee1Ename' Type='Lookup' " +
"List='Employee1' ShowField='Title'/>" +       
"<Field Name='Employee1Contact' Type='Lookup' " +
"List='Employee1' ShowField='NewColumn1'/>";  // Title & NewColumn1 is internal name
query.ViewFields =
"<FieldRef Name='Title'/>" +
"<FieldRef Name='Employee1Ename'/>" +
"<FieldRef Name='Employee1Contact'/>";
SPList list = web.Lists["Projects"];
SPListItemCollection coll = list.GetItems(query);

foreach (SPListItem item in coll){

SPFieldLookupValue Ename = new SPFieldLookupValue(item["Employee1Ename"].ToString());
SPFieldLookupValue contact = new SPFieldLookupValue(item["Employee1Contact"].ToString());
Console.WriteLine("{0} {1} {2}", item.Title, Ename.LookupValue,contact.LookupValue);}

Console.ReadKey();}
}
}
}