LINQ: Left Join

A left join is when each item in the first collection is returned regardless of whether it has any related items in the second collection.

For instance, say you want to find out how many orders were made for each product in your store.  You also want to see the products that did not have any orders so that you could decide whether to discontinue those items.  You can achieve this by a left outer join on products to order details using the ProductID key.

Query Syntax

var list = from prod in Products
           join od in OrderDetails on prod.ProductID equals od.ProductID into orderDetailList
           from odl in orderDetailList.DefaultIfEmpty()
           select new
           {
              id = prod.ProductID,
              name=prod.ProductName,
              quantity=odl.Quantity ?? 0
           };

Lambda Expression

var orders = Products
             .GroupJoin(OrderDetails, p => p.ProductID, d => d.ProductID, (p, od) => new {p, od})
             .SelectMany(g => g.od.DefaultIfEmpty(), (g, od) => new 
             { 
                id = g.p.ProductID,
                name = g.p.ProductName, 
                quantity = od.Quantity ?? 0
             });
 

Query Result:

SQL Query:

-- Region Parameters
DECLARE @p0 Int = 0
-- EndRegion
SELECT [t0].[ProductID] AS [id], [t0].[ProductName] AS [name], COALESCE([t1].[Quantity],@p0) AS [quantity]
FROM [Products] AS [t0]
LEFT OUTER JOIN [OrderDetails] AS [t1] ON [t0].[ProductID] = [t1].[ProductID]

Data Scripts:

create table Categories
(
 CategoryID int not null primary key,
 CategoryName varchar(100) not null
)

create table Products
(
 ProductID int not null primary key,
 ProductName varchar(100) not null,
 CategoryID int references Categories (CategoryID)
)

create table Orders
(
 OrderID int not null primary key,
 OrderDate DateTime,
 ShipCountry varchar(100) not null
)

create table OrderDetails
(
 OrderID int not null references Orders (OrderID),
 ProductID int not null references Products (ProductID),
 UnitPrice decimal,
 Quantity int,
 constraint PK_OrderDetails primary key (OrderID, ProductID)
)

insert Categories values (1, 'Seafood')
insert Categories values (2, 'Beverages')
insert Categories values (3, 'Confections')
insert Categories values (4, 'Meat/Poultry')
insert Categories values (5, 'Dairy Products')

insert Products values (1, 'Boston Crab Meat', 1)
insert Products values (2, 'Chai', 2)
insert Products values (3, 'Teatime Chocolate Biscuits', 3)
insert Products values (4, 'Milk', 5)
insert Products values (5, 'Cherry Pie', null)

insert Orders values (1, '2015-3-1', 'USA');
insert Orders values (2, '2015-3-12', 'USA');
insert Orders values (3, '2015-3-26', 'USA');

insert OrderDetails values (1, 1, 23.5, 2)
insert OrderDetails values (2, 2, 143, 5)
insert OrderDetails values (3, 3, 77, 1)
insert OrderDetails values (3, 2, 70, 3)