Join Operator

Home	Table Design

Inner - Join

This sample shows how to efficiently join elements based on equality between key expressions.

SQL:

SELECT Customer . [CustID] , Customer.[Name], Customer.[Sex], Customer.[Education], Customer.[IsStudent], Customer.[Age], Customer.[ContactNo], CustOrder.[CustOId],
CustOrder.[CustID] AS [CustID2], CustOrder.[Date], CustOrder.[Total]
FROM [dbo].[Customer] INNER JOIN [dbo].[CustOrder] ON Customer. [CustID]= CustOrder.[CustID]

DataClassesDataContext db = newDataClassesDataContext();

LINQ:

var result =
from c in db.Customers
join o in db.CustOrders on c.CustID equals o.CustID
select new { c, o};

foreach(var cust in result)
Response.Output.WriteLine("<br>Customer: " + cust.c.Name
+ " Date : " + cust.o.Date +" Total : " + cust.o.Total);

Result:

Customer: Rachit Rokad Date : 2/12/2010 12:00:00 AM Total : 1100
Customer: Ayush Patel Date : 2/10/2010 12:00:00 AM Total : 1000
Customer: Nenshi Date : 4/10/2010 12:00:00 AM Total : 800

Group Join with Left Outer Join

Using a group join you can get all the order that match a given customer. A left outer join is like a cross join, except that all the left hand side elements get included at least once, even if they don't match any right hand side elements.

SQL:

SELECT [Customer].[CustID], [Name], [Sex], [Education], [IsStudent], [Age], [ContactNo], [t1].[Date], [t1].[Total],(
SELECT COUNT(*)
FROM [dbo].[CustOrder] AS [t2]
WHERE [Customer].[CustID] = [t2].[CustID]
)AS [value]
FROM [dbo].[Customer]
LEFT OUTER JOIN [dbo].[CustOrder] AS [t1] ON
[Customer].[CustID] = [t1].[CustID]

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
join o in db.CustOrders on c.CustID equals o.CustID into Group
select new { c, Group };

foreach(var cust in result)
{
Response.Output.WriteLine("Customer: " + cust.c.Name);
foreach (var item in cust.Group)
Response.Output.WriteLine("<br>Date: " +item.Date +
" Total : " + item.Total);
Response.Output.WriteLine("<br> <br>");
}


Result:

Customer: Rachit Rokad
Date: 2/12/2010 12:00:00 AM Total : 1100

Customer: Poonam Makati

Customer: Ayush Patel
Date: 2/10/2010 12:00:00 AM Total : 1000

Customer: Nenshi
Date: 4/10/2010 12:00:00 AM Total : 800

Customer: Yuti Vachani

Cross Join with Group Join

The group join operator is more general than join, as this slightly more verbose version of the cross join sample shows.

SQL:

SELECT [Customer].CustID, Name, Sex, Education, IsStudent, Age,
ContactNo, [t1].[Date], [t1].[Total],(
SELECT COUNT(*)
FROM [dbo].[CustOrder] AS [t2]
WHERE CustID = [t2].[CustID])AS [value]
FROM Customer
CROSS JOIN CustOrder
LEFT OUTER JOIN [dbo].[CustOrder] AS [t1] ON [Customer].[CustID]=[t1].[CustID]
WHERE[Customer].[CustID] = CustOrder.CustID

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
join o in db.CustOrders on c.CustID equals o.CustID into Group
from o in Group
select new { c, Group };

foreach(var cust in result)
{
Response.Output.WriteLine("Customer: " + cust.c.Name);
foreach (var item in cust.Group)
Response.Output.WriteLine("<br>Date: " +item.Date + " Total : " + item.Total);
Response.Output.WriteLine("<br> <br>");
}

Result:
Customer: Rachit Rokad
Date: 2/12/2010 12:00:00 AM Total : 1100

Customer: Ayush Patel
Date: 2/10/2010 12:00:00 AM Total : 1000

Customer: Nenshi
Date: 4/10/2010 12:00:00 AM Total : 800


==============================================
Shradhdha Zalavadiya |Senior Software Engineer

WebMingle Technology
Accelerated by knowledge. Driven by values.
www.webMingle.in