How to work CAML Joins like SQL Joins?

Question

I've checked articles on here and Googled till I'm blue in the finger tips. I've read and read and read and just can't seem to wrap my head around CAML Joins in Sharepoint2010.

Question: Can someone please show me a full example of how exactly CAML Join Query compares to SQL Join Query?

*For Example (SQL of Course, just something to work with)*

If I had a Database named "whatever" & it contained two tables.  
We'll name these "tableA" & "tableB", respectively.

Let's say they look like this:

 - tableA - 
ID | Column1 | Column2 | Column3

 - tableB - 
ID | Column4 | Column5 | Column6

SELECT tableA.Column1, tableA.Column2, tableB.Column4 
FROM tableA 
INNER JOIN tableB ON tableA.ID = tableB.ID

Would give me something like:

 - newTable - 
Column1 | Column2 | Column4
 result | result  | result
 result | result  | result
 result | result  | result

So, again, my question is, can I get an exact example of this same operation is inacted upon in Sharepoint 2010 using the CAML Joins Query string?

Answer 1

Create your query from one of your lists.

 SPList list = SPContext.Current.Site.RootWeb.Lists["TableA"];
 SPQuery query = new SPQuery();

To do the join, set query.Joins to

 <Join Type="INNER" ListAlias="TableB">
    <Eq>
        <FieldRef Name="TableA" RefType="ID" />
        <FieldRef List="TableB" Name="ID" />
    </Eq>
 </Join>

and query.ProjectedFields to

  <Field Name="TableBColumn4" Type="Lookup" List="TableB" ShowField="Column4">

To choose the fields to display set query.ViewFields to

  <FieldRef Name="Column1">
  <FieldRef Name="Column2">
  <FieldRef Name="TableBColumn4">      

Then

SPListItemCollection result = tablea.GetItems(query);

Or something like that (it's from memory!)