Referential integrity implemented by Lookup columns in SharePoint 2010

The new added enhancements in CAML and SPquery are :

1. <Joins> element in CAML and the SPQuery.Joins property
2. <ProjectedFields> element in CAML and the SPQuery.ProjectedFields.
Joins - Joins in SharePoint are driven off of lookup-based columns.
For e.g. if we a have List1 with following column. City being the Lookup column.
Name
Address
City <strong>-> Lookup Column</strong>
and List2 with following columns
City
ZipCode
and if we have to access the Zipcode column from List2 along with the name and address from our List1 we will use a Join element in Our CAML to specify the our foreign key column i.e. City and will assign the CAML to SPquery.query property. We will then create the ProjectedFields element for specifying the ZipCode column (the column we need from List2) and assign it to the SPquery.ProjectedFields property.

ProjectedFields - These are fields in the List2, which you can access using CAML’s ProjectedFields element tag.
Let’s look at an example. In the below query i am trying to retrieve all the items from List1 where name =”isha” and the related zipcode field from the List2.
using (SPSite site = new SPSite(SPsiteUrl))
{
SPWeb web = site.OpenWeb();
SPQuery query = new SPQuery();
query.Query =
@”<Where><Eq>
<FieldRef Name=’Name’ /><Value Type=’Text’>Isha</Value>
</Eq></Where>”
query.Joins =
@”<Join Type=’LEFT’ ListAlias=’List2′>
<Eq>
<FieldRef Name=’City’ RefType=’Id’/>
<FieldRef List=’List2′ Name=’ID’/>
</Eq>
</Join>”;
query.ProjectedFields =
@”<Field
Name=’Zipcode’
Type=’Lookup’
List=’List2’
ShowField=’Zipcode’ />”;
SPListItemCollection items = web.Lists["List1"].GetItems(query);