内部結合の実行Perform inner joins

リレーショナル データベースでは、"内部結合" により、2 番目のコレクション内の一致するすべての要素に対して、最初のコレクションの各要素が一度表示される結果セットが生成されます。In relational database terms, an inner join produces a result set in which each element of the first collection appears one time for every matching element in the second collection. 最初のコレクション内の要素に一致する要素が存在しない場合、その要素は結果セットには表示されません。If an element in the first collection has no matching elements, it does not appear in the result set. Join メソッドは、C# の join 句によって呼び出され、内部結合を実装します。The Join method, which is called by the join clause in C#, implements an inner join.

このトピックでは、次の 4 種類の内部結合を実行する方法を示します。This topic shows you how to perform four variations of an inner join:

  • 簡単なキーに基づいて、2 つのデータ ソースの要素を関連付ける単純な内部結合。A simple inner join that correlates elements from two data sources based on a simple key.

  • "複合" キーに基づいて、2 つのデータ ソースの要素を関連付ける内部結合。An inner join that correlates elements from two data sources based on a composite key. 複合キーは複数の値で構成され、複数のプロパティに基づいて要素を関連付けることができます。A composite key, which is a key that consists of more than one value, enables you to correlate elements based on more than one property.

  • 一連の結合操作が相互に追加された "複数の結合"。A multiple join in which successive join operations are appended to each other.

  • グループ結合を使用して実装された内部結合。An inner join that is implemented by using a group join.

Example

簡単なキーの結合の例Simple key join example

次の例は、2 つのユーザー定義型オブジェクト、PersonPet が含まれた 2 つのコレクションを作成します。The following example creates two collections that contain objects of two user-defined types, Person and Pet. クエリでは、C# の join 句を使用して、Person オブジェクトを Owner がこの Person である Pet オブジェクトを照合します。The query uses the join clause in C# to match Person objects with Pet objects whose Owner is that Person. C# の select 句では、結果のオブジェクトの表示内容を定義します。The select clause in C# defines how the resulting objects will look. この例では、結果のオブジェクトは、飼い主の姓とペットの名前で構成される匿名型です。In this example the resulting objects are anonymous types that consist of the owner's first name and the pet's name.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

/// <summary>
/// Simple inner join.
/// </summary>
public static void InnerJoinExample()
{
    Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
    Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
    Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
    Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
    Person rui = new Person { FirstName = "Rui", LastName = "Raposo" };

    Pet barley = new Pet { Name = "Barley", Owner = terry };
    Pet boots = new Pet { Name = "Boots", Owner = terry };
    Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
    Pet bluemoon = new Pet { Name = "Blue Moon", Owner = rui };
    Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

    // Create two lists.
    List<Person> people = new List<Person> { magnus, terry, charlotte, arlene, rui };
    List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

    // Create a collection of person-pet pairs. Each element in the collection
    // is an anonymous type containing both the person's name and their pet's name.
    var query = from person in people
                join pet in pets on person equals pet.Owner
                select new { OwnerName = person.FirstName, PetName = pet.Name };

    foreach (var ownerAndPet in query)
    {
        Console.WriteLine($"\"{ownerAndPet.PetName}\" is owned by {ownerAndPet.OwnerName}");
    }
}

// This code produces the following output:
//
// "Daisy" is owned by Magnus
// "Barley" is owned by Terry
// "Boots" is owned by Terry
// "Whiskers" is owned by Charlotte
// "Blue Moon" is owned by Rui

LastName が "Huff" の Person オブジェクトは、Pet.Owner がその Person に等しい Pet オブジェクトがないため、結果セットに表示されません。Note that the Person object whose LastName is "Huff" does not appear in the result set because there is no Pet object that has Pet.Owner equal to that Person.

Example

複合キーの結合の例Composite key join example

1 つのプロパティだけに基づいて要素を関連付ける代わりに、複合キーを使用して、複数のプロパティに基づいて要素を比較できます。Instead of correlating elements based on just one property, you can use a composite key to compare elements based on multiple properties. これを行うには、各コレクションに対してキー セレクター関数を指定し、比較するプロパティで構成された匿名型を返します。To do this, specify the key selector function for each collection to return an anonymous type that consists of the properties you want to compare. プロパティにラベルを付ける場合は、各キーの匿名型に同じラベルを付ける必要があります。If you label the properties, they must have the same label in each key's anonymous type. また、プロパティは、同じ順序で表示する必要があります。The properties must also appear in the same order.

次の例は、Employee オブジェクトのリストと Student オブジェクトのリストを使用して、学生でもある社員を調べます。The following example uses a list of Employee objects and a list of Student objects to determine which employees are also students. これらの型の両方に、String 型の FirstName プロパティと LastName プロパティがあります。Both of these types have a FirstName and a LastName property of type String. それぞれのリストの要素から結合キーを作成する関数が、各要素の FirstName プロパティと LastName プロパティで構成された匿名型を返します。The functions that create the join keys from each list's elements return an anonymous type that consists of the FirstName and LastName properties of each element. 結合操作により、これらの複合キーが等しいかどうか比較され、それぞれのリストの氏名が一致するオブジェクトのペアが返されます。The join operation compares these composite keys for equality and returns pairs of objects from each list where both the first name and the last name match.

class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int EmployeeID { get; set; }
}

class Student
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int StudentID { get; set; }
}

/// <summary>
/// Performs a join operation using a composite key.
/// </summary>
public static void CompositeKeyJoinExample()
{
    // Create a list of employees.
    List<Employee> employees = new List<Employee> {
        new Employee { FirstName = "Terry", LastName = "Adams", EmployeeID = 522459 },
         new Employee { FirstName = "Charlotte", LastName = "Weiss", EmployeeID = 204467 },
         new Employee { FirstName = "Magnus", LastName = "Hedland", EmployeeID = 866200 },
         new Employee { FirstName = "Vernette", LastName = "Price", EmployeeID = 437139 } };

    // Create a list of students.
    List<Student> students = new List<Student> {
        new Student { FirstName = "Vernette", LastName = "Price", StudentID = 9562 },
        new Student { FirstName = "Terry", LastName = "Earls", StudentID = 9870 },
        new Student { FirstName = "Terry", LastName = "Adams", StudentID = 9913 } };

    // Join the two data sources based on a composite key consisting of first and last name,
    // to determine which employees are also students.
    IEnumerable<string> query = from employee in employees
                                join student in students
                                on new { employee.FirstName, employee.LastName }
                                equals new { student.FirstName, student.LastName }
                                select employee.FirstName + " " + employee.LastName;

    Console.WriteLine("The following people are both employees and students:");
    foreach (string name in query)
        Console.WriteLine(name);
}

// This code produces the following output:
//
// The following people are both employees and students:
// Terry Adams
// Vernette Price

Example

複数の結合の例Multiple join example

任意の数の結合操作を相互に追加して、複数の結合を実行できます。Any number of join operations can be appended to each other to perform a multiple join. C# の各 join 句は、指定されたデータ ソースを前の結合の結果に関連付けます。Each join clause in C# correlates a specified data source with the results of the previous join.

次の例は、Person オブジェクトのリスト、Cat オブジェクトのリスト、Dog オブジェクトのリストの 3 つのコレクションを作成します。The following example creates three collections: a list of Person objects, a list of Cat objects, and a list of Dog objects.

C# の最初の join 句では、Cat.Owner と一致する Person オブジェクトに基づいて飼い主と猫を一致させます。The first join clause in C# matches people and cats based on a Person object matching Cat.Owner. この操作で、Person オブジェクトと Cat.Name が含まれた匿名型のシーケンスが返されます。It returns a sequence of anonymous types that contain the Person object and Cat.Name.

C# の 2 番目の join 句では、Person 型の Owner プロパティと動物の名前の最初の文字で構成される複合キーに基づいて、最初の結合で返された匿名型を、指定された犬のリストの Dog オブジェクトに関連付けます。The second join clause in C# correlates the anonymous types returned by the first join with Dog objects in the supplied list of dogs, based on a composite key that consists of the Owner property of type Person, and the first letter of the animal's name. この操作で、一致するそれぞれのペアの Cat.Name プロパティと Dog.Name プロパティが含まれた匿名型のシーケンスが返されます。It returns a sequence of anonymous types that contain the Cat.Name and Dog.Name properties from each matching pair. これは内部結合であるため、2 番目のデータ ソースに一致するものが存在する、最初のデータ ソースのオブジェクトのみが返されます。Because this is an inner join, only those objects from the first data source that have a match in the second data source are returned.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

class Cat : Pet
{ }

class Dog : Pet
{ }

public static void MultipleJoinExample()
{
    Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
    Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
    Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
    Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
    Person rui = new Person { FirstName = "Rui", LastName = "Raposo" };
    Person phyllis = new Person { FirstName = "Phyllis", LastName = "Harris" };

    Cat barley = new Cat { Name = "Barley", Owner = terry };
    Cat boots = new Cat { Name = "Boots", Owner = terry };
    Cat whiskers = new Cat { Name = "Whiskers", Owner = charlotte };
    Cat bluemoon = new Cat { Name = "Blue Moon", Owner = rui };
    Cat daisy = new Cat { Name = "Daisy", Owner = magnus };

    Dog fourwheeldrive = new Dog { Name = "Four Wheel Drive", Owner = phyllis };
    Dog duke = new Dog { Name = "Duke", Owner = magnus };
    Dog denim = new Dog { Name = "Denim", Owner = terry };
    Dog wiley = new Dog { Name = "Wiley", Owner = charlotte };
    Dog snoopy = new Dog { Name = "Snoopy", Owner = rui };
    Dog snickers = new Dog { Name = "Snickers", Owner = arlene };

    // Create three lists.
    List<Person> people =
        new List<Person> { magnus, terry, charlotte, arlene, rui, phyllis };
    List<Cat> cats =
        new List<Cat> { barley, boots, whiskers, bluemoon, daisy };
    List<Dog> dogs =
        new List<Dog> { fourwheeldrive, duke, denim, wiley, snoopy, snickers };

    // The first join matches Person and Cat.Owner from the list of people and
    // cats, based on a common Person. The second join matches dogs whose names start
    // with the same letter as the cats that have the same owner.
    var query = from person in people
                join cat in cats on person equals cat.Owner
                join dog in dogs on 
                new { Owner = person, Letter = cat.Name.Substring(0, 1) }
                equals new { dog.Owner, Letter = dog.Name.Substring(0, 1) }
                select new { CatName = cat.Name, DogName = dog.Name };

    foreach (var obj in query)
    {
        Console.WriteLine( 
            $"The cat \"{obj.CatName}\" shares a house, and the first letter of their name,
            with \"{obj.DogName}\".");  
    }
}

// This code produces the following output:
//
// The cat "Daisy" shares a house, and the first letter of their name, with "Duke".
// The cat "Whiskers" shares a house, and the first letter of their name, with "Wiley".

Example

グループ化結合を使用した内部結合の例Inner join by using grouped join example

グループ結合を使用して内部結合を実装する方法を次の例に示します。The following example shows you how to implement an inner join by using a group join.

query1 で、Person オブジェクトのリストは、Pet.Owner プロパティと一致する Person に基づいて、Pet オブジェクトのリストにグループ結合されます。In query1, the list of Person objects is group-joined to the list of Pet objects based on the Person matching the Pet.Owner property. グループ結合によって、それぞれのグループが Person オブジェクトおよび一致する Pet オブジェクトのシーケンスで構成された、中間グループのコレクションが作成されます。The group join creates a collection of intermediate groups, where each group consists of a Person object and a sequence of matching Pet objects.

2 番目の from 句をクエリに追加すると、シーケンスのシーケンスが 1 つの長いシーケンスに結合 (または平坦化) されます。By adding a second from clause to the query, this sequence of sequences is combined (or flattened) into one longer sequence. 最後のシーケンスの要素の型は、select 句で指定されます。The type of the elements of the final sequence is specified by the select clause. この例では、この型は、一致する各ペアの Person.FirstName プロパティと Pet.Name プロパティで構成された匿名型です。In this example, that type is an anonymous type that consists of the Person.FirstName and Pet.Name properties for each matching pair.

query1 の結果は、into 句のない join 句を使用して内部結合を実行することで得られた結果セットと同じです。The result of query1 is equivalent to the result set that would have been obtained by using the join clause without the into clause to perform an inner join. query2 変数は、これと同等のクエリを示しています。The query2 variable demonstrates this equivalent query.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

/// <summary>
/// Performs an inner join by using GroupJoin().
/// </summary>
public static void InnerGroupJoinExample()
{
    Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
    Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
    Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
    Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

    Pet barley = new Pet { Name = "Barley", Owner = terry };
    Pet boots = new Pet { Name = "Boots", Owner = terry };
    Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
    Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
    Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

    // Create two lists.
    List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
    List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

    var query1 = from person in people
                 join pet in pets on person equals pet.Owner into gj
                 from subpet in gj
                 select new { OwnerName = person.FirstName, PetName = subpet.Name };

    Console.WriteLine("Inner join using GroupJoin():");
    foreach (var v in query1)
    {
        Console.WriteLine($"{v.OwnerName} - {v.PetName}"));
    }

    var query2 = from person in people
                 join pet in pets on person equals pet.Owner
                 select new { OwnerName = person.FirstName, PetName = pet.Name };
        
    Console.WriteLine("\nThe equivalent operation using Join():");
    foreach (var v in query2)
        Console.WriteLine($"{v.OwnerName} - {v.PetName}"));
}

// This code produces the following output:
//
// Inner join using GroupJoin():
// Magnus - Daisy
// Terry - Barley
// Terry - Boots
// Terry - Blue Moon
// Charlotte - Whiskers
//
// The equivalent operation using Join():
// Magnus - Daisy
// Terry - Barley
// Terry - Boots
// Terry - Blue Moon
// Charlotte - Whiskers

関連項目See also

Join
GroupJoin
グループ化結合の実行Perform grouped joins
左外部結合の実行Perform left outer joins
匿名型Anonymous types