join clause (C# Reference)

2025-04-15

子句 join 適合用來關聯物件模型中不同來源序列的元素，這些元素之間沒有直接關係。唯一的需求是每個來源中的元素會共用一些可比較是否相等的值。例如，食品轉銷商可能有特定產品的供應商清單，以及買家清單。例如，您可以使用join子句來建立位於相同指定區域的該產品之供應商和買家的清單。

A join clause takes two source sequences as input. 每個序列中的元素必須是或包含屬性，該屬性可以與其他序列中的對應屬性進行比較。 The join clause compares the specified keys for equality by using the special equals keyword. All joins performed by the join clause are equijoins. 您執行的特定聯結類型將決定 join 子句的輸出形狀。以下是三種最常見的聯結類型：

Inner join
Group join
Left outer join

Inner join

下列範例顯示簡單的內部等聯結。此查詢會產生「產品名稱/類別」配對的一般序列。相同的類別字串會出現在多個元素中。如果中的 categories 項目沒有相符 products的，該類別就不會出現在結果中。

var innerJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID
    select new { ProductName = prod.Name, Category = category.Name }; //produces flat sequence

如需詳細資訊，請參閱執行內部聯結。

Group join

A join clause with an into expression is called a group join.

var innerGroupJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    select new { CategoryName = category.Name, Products = prodGroup };

群組聯結會產生階層式結果序列，將左側來源序列中的元素與右側來源序列中的一或多個相符專案產生關聯。 A group join has no equivalent in relational terms; it is essentially a sequence of object arrays.

如果找不到來自右來源序列的元素與左來源中的元素相匹配，則 join 子句將會對該項目產生一個空陣列。因此，群組聯結基本上仍然是內部等聯結，不同之處在於結果序列會組織成群組。

If you just select the results of a group join, you can access the items, but you cannot identify the key that they match on. Therefore, it is generally more useful to select the results of the group join into a new type that also has the key name, as shown in the previous example.

當然，您也可以使用群組聯結的結果作為另一個子查詢的產生器：

var innerGroupJoinQuery2 =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from prod2 in prodGroup
    where prod2.UnitPrice > 2.50M
    select prod2;

如需詳細資訊，請參閱執行群組聯結。

Left outer join

在左外部連接中，即使在右側序列中沒有相符的元素，仍會傳回左側來源序列中的所有元素。若要在 LINQ 中執行左外部聯結，請使用 DefaultIfEmpty 方法搭配群組聯結來指定預設的右側元素，以在左側元素沒有相符項目時產生。您可以使用 null 做為任何參考型別的預設值，也可以指定使用者定義的默認類型。在下列範例中，會顯示使用者定義的默認類型：

var leftOuterJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from item in prodGroup.DefaultIfEmpty(new Product { Name = String.Empty, CategoryID = 0 })
    select new { CatName = category.Name, ProdName = item.Name };

For more information, see Perform left outer joins.

The equals operator

A join clause performs an equijoin. 換句話說，您只能根據兩個鍵值的相等性進行匹配。不支援其他類型的比較，例如「大於」或「不相等」。為了明確表示所有聯結都是等聯結， join 子句會使用 equals 關鍵詞，而不是 == 運算符。 equals 關鍵詞只能在 join 子句中使用，且在某些重要方面與 == 運算符不同。比較字串時， equals 具有要依值比較的多載，而運算符 == 會使用參考相等。當比較的兩端都有相同的字串變數， equals 並 == 達到相同的結果：true。這是因為，當程式宣告兩個以上的對等字串變數時，編譯程式會將所有變數儲存在相同的位置。 This is known as interning. Another important difference is the null comparison: null equals null is evaluated as false with equals operator, instead of == operator that evaluates it as true. 最後，範圍行為不同：使用 equals時，左鍵會取用外部來源序列，而右鍵會取用內部來源。外部來源只位於左側 equals 的範圍中，而內部來源序列只位於右側的範圍中。

Non-equijoins

您可以使用多個 from 子句，將新的序列獨立導入查詢，以執行非等聯結、交叉聯結和其他自定義聯結作業。如需詳細資訊，請參閱執行自定義聯結作業。

Joins on object collections vs. relational tables

在 LINQ 查詢表示式中，聯結作業會在物件集合上執行。物件集合不能以與兩個關係型數據表完全相同的方式「聯結」。在 LINQ 中，只有在兩個源序列不存有任何關聯性時，才需要明確的 join 子句。使用 LINQ to SQL 時，外鍵資料表在物件模型中會作為主資料表的屬性來表示。例如，在 Northwind 資料庫中，Customer 數據表與 Orders 數據表有外鍵關聯性。當您將數據表對應至物件模型時，Customer 類別具有 Orders 屬性，其中包含與該 Customer 相關聯的 Orders 集合。 In effect, the join has already been done for you.

For more information about querying across related tables in the context of LINQ to SQL, see How to: Map Database Relationships.

複合鍵

您可以使用複合索引鍵來測試多個值的相等。如需詳細資訊，請參閱使用複合鍵進行聯結。複合鍵也可以在group子句中使用。

範例

下列範例會使用相同的配對鍵，比較相同資料來源上內部聯結、群組聯結和左外部聯結的結果。這些範例會新增一些額外的程序代碼，以釐清控制台顯示的結果。

class JoinDemonstration
{
    #region Data

    class Product
    {
        public required string Name { get; init; }
        public required int CategoryID { get; init; }
    }

    class Category
    {
        public required string Name { get; init; }
        public required int ID { get; init; }
    }

    // Specify the first data source.
    List<Category> categories =
    [
        new Category {Name="Beverages", ID=001},
        new Category {Name="Condiments", ID=002},
        new Category {Name="Vegetables", ID=003},
        new Category {Name="Grains", ID=004},
        new Category {Name="Fruit", ID=005}
    ];

    // Specify the second data source.
    List<Product> products =
    [
      new Product {Name="Cola",  CategoryID=001},
      new Product {Name="Tea",  CategoryID=001},
      new Product {Name="Mustard", CategoryID=002},
      new Product {Name="Pickles", CategoryID=002},
      new Product {Name="Carrots", CategoryID=003},
      new Product {Name="Bok Choy", CategoryID=003},
      new Product {Name="Peaches", CategoryID=005},
      new Product {Name="Melons", CategoryID=005},
    ];
    #endregion

    static void Main(string[] args)
    {
        JoinDemonstration app = new JoinDemonstration();

        app.InnerJoin();
        app.GroupJoin();
        app.GroupInnerJoin();
        app.GroupJoin3();
        app.LeftOuterJoin();
        app.LeftOuterJoin2();
    }

    void InnerJoin()
    {
        // Create the query that selects
        // a property from each element.
        var innerJoinQuery =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID
           select new { Category = category.ID, Product = prod.Name };

        Console.WriteLine("InnerJoin:");
        // Execute the query. Access results
        // with a simple foreach statement.
        foreach (var item in innerJoinQuery)
        {
            Console.WriteLine("{0,-10}{1}", item.Product, item.Category);
        }
        Console.WriteLine($"InnerJoin: {innerJoinQuery.Count()} items in 1 group.");
        Console.WriteLine(System.Environment.NewLine);
    }

    void GroupJoin()
    {
        // This is a demonstration query to show the output
        // of a "raw" group join. A more typical group join
        // is shown in the GroupInnerJoin method.
        var groupJoinQuery =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID into prodGroup
           select prodGroup;

        // Store the count of total items (for demonstration only).
        int totalItems = 0;

        Console.WriteLine("Simple GroupJoin:");

        // A nested foreach statement is required to access group items.
        foreach (var prodGrouping in groupJoinQuery)
        {
            Console.WriteLine("Group:");
            foreach (var item in prodGrouping)
            {
                totalItems++;
                Console.WriteLine("   {0,-10}{1}", item.Name, item.CategoryID);
            }
        }
        Console.WriteLine($"Unshaped GroupJoin: {totalItems} items in {groupJoinQuery.Count()} unnamed groups");
        Console.WriteLine(System.Environment.NewLine);
    }

    void GroupInnerJoin()
    {
        var groupJoinQuery2 =
            from category in categories
            orderby category.ID
            join prod in products on category.ID equals prod.CategoryID into prodGroup
            select new
            {
                Category = category.Name,
                Products = from prod2 in prodGroup
                           orderby prod2.Name
                           select prod2
            };

        //Console.WriteLine("GroupInnerJoin:");
        int totalItems = 0;

        Console.WriteLine("GroupInnerJoin:");
        foreach (var productGroup in groupJoinQuery2)
        {
            Console.WriteLine(productGroup.Category);
            foreach (var prodItem in productGroup.Products)
            {
                totalItems++;
                Console.WriteLine("  {0,-10} {1}", prodItem.Name, prodItem.CategoryID);
            }
        }
        Console.WriteLine($"GroupInnerJoin: {totalItems} items in {groupJoinQuery2.Count()} named groups");
        Console.WriteLine(System.Environment.NewLine);
    }

    void GroupJoin3()
    {

        var groupJoinQuery3 =
            from category in categories
            join product in products on category.ID equals product.CategoryID into prodGroup
            from prod in prodGroup
            orderby prod.CategoryID
            select new { Category = prod.CategoryID, ProductName = prod.Name };

        //Console.WriteLine("GroupInnerJoin:");
        int totalItems = 0;

        Console.WriteLine("GroupJoin3:");
        foreach (var item in groupJoinQuery3)
        {
            totalItems++;
            Console.WriteLine($"   {item.ProductName}:{item.Category}");
        }

        Console.WriteLine($"GroupJoin3: {totalItems} items in 1 group");
        Console.WriteLine(System.Environment.NewLine);
    }

    void LeftOuterJoin()
    {
        // Create the query.
        var leftOuterQuery =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID into prodGroup
           select prodGroup.DefaultIfEmpty(new Product() { Name = "Nothing!", CategoryID = category.ID });

        // Store the count of total items (for demonstration only).
        int totalItems = 0;

        Console.WriteLine("Left Outer Join:");

        // A nested foreach statement  is required to access group items
        foreach (var prodGrouping in leftOuterQuery)
        {
            Console.WriteLine("Group:");
            foreach (var item in prodGrouping)
            {
                totalItems++;
                Console.WriteLine("  {0,-10}{1}", item.Name, item.CategoryID);
            }
        }
        Console.WriteLine($"LeftOuterJoin: {totalItems} items in {leftOuterQuery.Count()} groups");
        Console.WriteLine(System.Environment.NewLine);
    }

    void LeftOuterJoin2()
    {
        // Create the query.
        var leftOuterQuery2 =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID into prodGroup
           from item in prodGroup.DefaultIfEmpty()
           select new { Name = item == null ? "Nothing!" : item.Name, CategoryID = category.ID };

        Console.WriteLine($"LeftOuterJoin2: {leftOuterQuery2.Count()} items in 1 group");
        // Store the count of total items
        int totalItems = 0;

        Console.WriteLine("Left Outer Join 2:");

        // Groups have been flattened.
        foreach (var item in leftOuterQuery2)
        {
            totalItems++;
            Console.WriteLine("{0,-10}{1}", item.Name, item.CategoryID);
        }
        Console.WriteLine($"LeftOuterJoin2: {totalItems} items in 1 group");
    }
}
/*Output:

InnerJoin:
Cola      1
Tea       1
Mustard   2
Pickles   2
Carrots   3
Bok Choy  3
Peaches   5
Melons    5
InnerJoin: 8 items in 1 group.


Unshaped GroupJoin:
Group:
    Cola      1
    Tea       1
Group:
    Mustard   2
    Pickles   2
Group:
    Carrots   3
    Bok Choy  3
Group:
Group:
    Peaches   5
    Melons    5
Unshaped GroupJoin: 8 items in 5 unnamed groups


GroupInnerJoin:
Beverages
    Cola       1
    Tea        1
Condiments
    Mustard    2
    Pickles    2
Vegetables
    Bok Choy   3
    Carrots    3
Grains
Fruit
    Melons     5
    Peaches    5
GroupInnerJoin: 8 items in 5 named groups


GroupJoin3:
    Cola:1
    Tea:1
    Mustard:2
    Pickles:2
    Carrots:3
    Bok Choy:3
    Peaches:5
    Melons:5
GroupJoin3: 8 items in 1 group


Left Outer Join:
Group:
    Cola      1
    Tea       1
Group:
    Mustard   2
    Pickles   2
Group:
    Carrots   3
    Bok Choy  3
Group:
    Nothing!  4
Group:
    Peaches   5
    Melons    5
LeftOuterJoin: 9 items in 5 groups


LeftOuterJoin2: 9 items in 1 group
Left Outer Join 2:
Cola      1
Tea       1
Mustard   2
Pickles   2
Carrots   3
Bok Choy  3
Nothing!  4
Peaches   5
Melons    5
LeftOuterJoin2: 9 items in 1 group
Press any key to exit.
*/

備註

A join clause that is not followed by into is translated into a Join method call. A join clause that is followed by into is translated to a GroupJoin method call.

共用方式為