{"id":2261,"date":"2012-07-13T15:00:00","date_gmt":"2012-07-13T15:00:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/powershell\/2012\/07\/13\/join-object\/"},"modified":"2019-02-18T13:05:37","modified_gmt":"2019-02-18T20:05:37","slug":"join-object","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/powershell\/join-object\/","title":{"rendered":"Join-Object"},"content":{"rendered":"

Problem:<\/span><\/h2>\n

How do I join two lists of objects into a single list? Or the equivalent: How do I join two CSV files into a single one?<\/span><\/p>\n

Note that joining makes sense only when the two lists or CSV files have something in common.<\/span><\/p>\n

 <\/p>\n

Why do we need Join?<\/span><\/h2>\n

If <\/span>you know why you need join would not enjoy a review of basic database concepts, <\/span>feel free to skip this section.<\/span><\/p>\n

Say <\/span>you own a company and want to keep track of the time when your employees <\/span>arrive. A simplified table of employees could look like this:<\/span><\/p>\n

\n\n\n\n\n\n\n\n
\n

Employee<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

Id<\/b><\/span><\/p>\n<\/td>\n

\n

Name<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/span><\/p>\n<\/td>\n

\n

John<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

2<\/span><\/p>\n<\/td>\n

\n

Mark<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

3<\/span><\/p>\n<\/td>\n

\n

Hanna<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/span> <\/p>\n

The <\/span>id uniquely identifies an employee because two employees can have the same <\/span>name. A full table would have more employees. It would also have last name, <\/span>first name, the date the employee joined the company, and other columns.<\/span><\/p>\n

The <\/span>Entrance table that contains each employee’s arrival time might look like this:<\/span><\/p>\n

\n\n\n\n\n\n\n\n\n\n\n
\n

Entrance<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

Employee  Id<\/b><\/span><\/p>\n<\/td>\n

\n

When<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/span><\/p>\n<\/td>\n

\n

6\/12\/2012 08:05:01 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/span><\/p>\n<\/td>\n

\n

6\/13\/2012 07:59:12 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/span><\/p>\n<\/td>\n

\n

6\/14\/2012 07:49:10 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

2<\/span><\/p>\n<\/td>\n

\n

6\/12\/2012 10:33:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

2<\/span><\/p>\n<\/td>\n

\n

6\/13\/2012 10:15:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

44<\/span><\/p>\n<\/td>\n

\n

2\/29\/2012 01:00:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

 <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

<\/span> <\/p>\n

The “When” <\/span>column has both the date and the time. Types like DateTime are common for .Net <\/span>Objects and databases.<\/span><\/p>\n

We can <\/span>conclude the following from the data:<\/span><\/p>\n

    \n
  1. John <\/span>(the employee with id 1) seems to arrive early; at around 8.<\/span><\/li>\n
  2. Mark <\/span>(the employee with id 2) is not a morning person (I like him already).<\/span><\/li>\n
  3. Mary <\/span>is more of a “work from home” kind of person. She has never entered the company <\/span>headquarters.<\/span><\/li>\n
  4. What <\/span>about the Employee with id 44? There is no such employee in our table! And what <\/span>nefarious thing was he doing at 1:00 am on February 29? I wonder if he will be coming <\/span>back in 4 years… <\/span>Employee <\/span>44 seems to be a mistake. Maybe the data was entered incorrectly. In some <\/span>databases, you can use “referential integrity” to make sure that such mistakes <\/span>never make into your table data. Obviously, this system doesn’t have a check <\/span>like that.<\/span><\/li>\n<\/ol>\n

    Wait <\/span>a minute, why do we have two tables instead of one? <\/span><\/p>\n

    A simple one-table solution <\/span>might look like this:<\/span><\/p>\n

    \n\n\n\n\n\n\n\n\n\n
    \n

    Employee Entrance<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Employee<\/b><\/span><\/p>\n<\/td>\n

    		\n

    When<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    John<\/span><\/p>\n<\/td>\n

    		\n

    6\/12\/2012 08:05:01 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    John<\/span><\/p>\n<\/td>\n

    		\n

    6\/13\/2012 07:59:12 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    John<\/span><\/p>\n<\/td>\n

    		\n

    6\/14\/2012 07:49:10 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Mark<\/span><\/p>\n<\/td>\n

    		\n

    6\/12\/2012 10:33:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Mark<\/span><\/p>\n<\/td>\n

    		\n

    6\/13\/2012 10:15:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

     <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    Before <\/span>throwing stones at this solution, I should say this could work for the simplest <\/span>of cases. As we are going to see below, it doesn’t work if things get a bit <\/span>more complex, so it is not future-proof. Now, for more specific stones:<\/span><\/p>\n

    In <\/span>the table above, John’s 3 entrances and Mark’s 2 entrances are recorded, but I <\/span>omitted the Entrance for id 44 that we used to have, because he doesn’t have a <\/span>name. If we consider that we can have 2 Johns working for the company, we will <\/span>need an ID, as seen below. We can also have more data for each employee, like <\/span>their Last Name:<\/span><\/p>\n

    \n\n\n\n\n\n\n\n\n\n
    \n

    Employee Entrance<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Id<\/b><\/span><\/p>\n<\/td>\n

    		\n

    First Name<\/b><\/span><\/p>\n<\/td>\n

    		\n

    Last Name<\/b><\/span><\/p>\n<\/td>\n

    		\n

    When<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    1<\/span><\/p>\n<\/td>\n

    		\n

    John<\/span><\/p>\n<\/td>\n

    		\n

    Smith<\/span><\/p>\n<\/td>\n

    		\n

    6\/12\/2012 08:05:01 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    1<\/span><\/p>\n<\/td>\n

    		\n

    John<\/span><\/p>\n<\/td>\n

    		\n

    Smith<\/span><\/p>\n<\/td>\n

    		\n

    6\/13\/2012 07:59:12 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    1<\/span><\/p>\n<\/td>\n

    		\n

    John<\/span><\/p>\n<\/td>\n

    		\n

    Smith<\/span><\/p>\n<\/td>\n

    		\n

    6\/14\/2012 07:49:10 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    2<\/span><\/p>\n<\/td>\n

    		\n

    Mark<\/span><\/p>\n<\/td>\n

    		\n

    West<\/span><\/p>\n<\/td>\n

    		\n

    6\/12\/2012 10:33:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    2<\/span><\/p>\n<\/td>\n

    		\n

    Mark<\/span><\/p>\n<\/td>\n

    		\n

    West<\/span><\/p>\n<\/td>\n

    		\n

    6\/13\/2012 10:15:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

     <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

    <\/span> <\/p>\n

     <\/p>\n

    This re<\/span>petition of first name and last name is one of the reasons we have two <\/span>tables. Having two tables allows us to have an Entrance Table, where we repeat <\/span>just the Employee Id, a small amount of data. This way, the tables can evolve <\/span>separately. If we start adding columns for Employees, like Address, Phone, <\/span>etc., nothing changes in the Entrance table.<\/span><\/p>\n

    There <\/span>is one more fundamental reason to have two tables. Say we want to start keeping <\/span>track of employee payments. If the employees are in the same table as their entrance, <\/span>how do add the payments? It clearly does not fit in the same table, because <\/span>there would be multiple payments per employee and there are already multiple <\/span>employee entries for their entrances.<\/span><\/p>\n

    The <\/span>proper way to express the concept of one of Employee and multiple entrances is <\/span>to have two tables. Then, it is going to be simple to add columns to the <\/span>Employee table or add another table for their multiple payments. Another way to <\/span>say this is that a one-to-many relationship, like one employee to many payments, <\/span>or one employee to many entrances, needs a separate table. <\/span><\/p>\n

    It <\/span>turns out that problems we can easily describe conceptually can often require many <\/span>tables.<\/span><\/span><\/p>\n

    For <\/span>example, let’s consider keeping track of sales at a fruit stand. That seems <\/span>simple enough conceptually, but let’s see what is involved in this task. We <\/span>have customers, fruit (our product), and sales. Each sale receipt has one or <\/span>more lines, in which each line describes a product and its quantity. The tables <\/span>we have are:<\/span><\/p>\n

      \n
    1. The customer table with name, delivery address, phone, etc.<\/li>\n
    2. The product table with name, price, etc.<\/li>\n
    3. The sales table with date and customer.<\/li>\n
    4. The sale item table with the sale (id), product (id) and quantity.<\/li>\n<\/ol>\n

      The information on a customer receipt would be gathered from all 4 tables and we would need to join this information from multiple tables.<\/p>\n

      If <\/span>even simple conceptual problems require many tables, joining tables becomes a <\/span>fundamental need, which brings us to the next section.<\/span><\/p>\n

       <\/p>\n

      The different types of Joins<\/span><\/h2>\n

      Let’s look <\/span>at our entrance data again.<\/span><\/p>\n\n\n\n
      \n\n\n\n\n\n\n\n
      \n

      Employee<\/b><\/p>\n<\/td>\n<\/tr>\n

      \n

      Id<\/b><\/p>\n<\/td>\n

      		\n

      Name<\/b><\/p>\n<\/td>\n<\/tr>\n

      \n

      1<\/p>\n<\/td>\n

      		\n

      John<\/p>\n<\/td>\n<\/tr>\n

      \n

      2<\/p>\n<\/td>\n

      		\n

      Mark<\/p>\n<\/td>\n<\/tr>\n

      \n

      3<\/p>\n<\/td>\n

      		\n

      Hanna<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n

      		\n\n\n\n\n\n\n\n\n\n\n
      \n

      Entrance<\/b><\/p>\n<\/td>\n<\/tr>\n

      \n

      Employee  Id<\/b><\/p>\n<\/td>\n

      		\n

      When<\/b><\/p>\n<\/td>\n<\/tr>\n

      \n

      1<\/p>\n<\/td>\n

      		\n

      6\/12\/2012 08:05:01 AM<\/p>\n<\/td>\n<\/tr>\n

      \n

      1<\/p>\n<\/td>\n

      		\n

      6\/13\/2012 07:59:12 AM<\/p>\n<\/td>\n<\/tr>\n

      \n

      1<\/p>\n<\/td>\n

      		\n

      6\/14\/2012 07:49:10 AM<\/p>\n<\/td>\n<\/tr>\n

      \n

      2<\/p>\n<\/td>\n

      		\n

      6\/12\/2012 10:33:00 AM<\/p>\n<\/td>\n<\/tr>\n

      \n

      2<\/p>\n<\/td>\n

      		\n

      6\/13\/2012 10:15:00 AM<\/p>\n<\/td>\n<\/tr>\n

      \n

      44<\/p>\n<\/td>\n

      		\n

      2\/29\/2012 01:00:00 AM<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      Now that <\/span>the tables are separate and future-proof, we might want a view of the data that <\/span>joins the name and entrance date\/time for each employee. This can be done in 4 different <\/span>ways:<\/span><\/p>\n

       1) Only the Employees with Entrances and Entrances with an Employee (SQL <\/span>Inner Join)<\/span><\/strong><\/p>\n

      This join shows all <\/span>the employee entrances except for the Entrance for Employee 44 that has no <\/span>corresponding Id. It also does not show a line for Hanna because there is no <\/span>entry for her in the Entrance table.<\/span><\/p>\n

      \n\n\n\n\n\n\n\n\n
      \n

      Name<\/b><\/span><\/p>\n<\/td>\n

      		\n

      When<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 08:05:01 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 07:59:12 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/14\/2012 07:49:10 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 10:33:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 10:15:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

       <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      An example <\/span>of a SQL statement that would perform this join looks like this:<\/span><\/p>\n

      SELECT   Employee.Name<\/b>,<\/span>Entrance.[When]<\/b><\/span><\/p>\n

      FROM    Employee<\/b><\/span>
      INNER JOIN<\/i> Entrance<\/b> ON Employee.id<\/b> = Entrance.EmployeeId<\/b><\/span><\/p>\n

      Select<\/i> enumerates the columns, which are <\/span>Name from the Employee table, and When from the Entrance table. The square brackets <\/span>surrounding “[<\/b>When]<\/b>” <\/span>distinguish the “When”  column <\/span>name from the “When” SQL command. This is required only for SQL <\/span>language keywords.<\/span><\/p>\n

      Employee is “inner <\/span>joined” to Entrance by the statement: Employee.id = Entrance.EmployeeId.<\/span><\/p>\n

      Inner Join is the <\/span>name SQL gives to this kind of join where an item appears in the result only if <\/span>the join statement (Employee.id = Entrance.EmployeeId) is true in both<\/strong> tables <\/span>being joined. Hanna has her ID of 3, but there is no 3 in the Entrance table to <\/span>make this statement true. Employee 44 has his Entrance but there is no ID in <\/span>the Employee table corresponding to 44 that would make the statement true.<\/span><\/p>\n

       <\/p>\n

      2) All Employees (SQL Left Outer Join)<\/b><\/span><\/p>\n

      This join shows all employees, <\/span>including Hanna who has no corresponding entry in the Entrance table.<\/span><\/p>\n

      \n\n\n\n\n\n\n\n\n\n
      \n

      Name<\/b><\/span><\/p>\n<\/td>\n

      		\n

      When<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 08:05:01 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 07:59:12 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/14\/2012 07:49:10 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 10:33:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 10:15:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Hanna<\/span><\/p>\n<\/td>\n

      		\n

       <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

       <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      The SQL Statement looks like:<\/span><\/p>\n

      SELECT   Employee.Name<\/b>, <\/span>Entrance.[When]<\/b><\/span><\/p>\n

      FROM    Employee<\/b><\/span>
      LEFT OUTER JOIN<\/i> Entrance<\/b> ON Employee.id = Entrance.EmployeeId<\/b><\/span><\/p>\n

      The <\/span>only change in this second SQL statement is the use of “LEFT OUTER JOIN” <\/span>(instead of inner join).<\/span><\/p>\n

      Left <\/span>refers to the fact that all<\/strong> elements of the left table in “Employee LEFT OUTER <\/span>JOIN Entrance” (which is Employee) will appear in the result. “<\/span><\/p>\n

      OUTER <\/span>is in opposition to the INNER join and it allows Hanna to be listed even though <\/span>her Employee.id is not in any of the Entrance’s EmployeeIds.<\/span><\/p>\n

      Note: <\/span>Some SQL variants will not use “OUTER”.<\/span><\/p>\n

       <\/p>\n

      3) All Entrances (SQL Right Outer Join)<\/b><\/span><\/p>\n

      This <\/span>join has all of the Entrances, including the entry for Employee 44 even <\/span>though there is no corresponding employee Id for that in the Employee’s table.<\/span><\/p>\n

      \n\n\n\n\n\n\n\n\n\n
      \n

      Name<\/b><\/span><\/p>\n<\/td>\n

      		\n

      When<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 08:05:01 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 07:59:12 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/14\/2012 07:49:10 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 10:33:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 10:15:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

       <\/p>\n<\/td>\n

      		\n

      2\/29\/2012 01:00:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

       <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> The SQL Statement looks like:<\/span><\/p>\n

      SELECT   Employee.Name<\/b>, <\/span>Entrance.[When]<\/b><\/span><\/p>\n

      FROM    Employee<\/b><\/span>
      RIGHT OUTER JOIN<\/i> Entrance<\/b> ON Employee.id = Entrance.EmployeeId<\/b><\/span><\/p>\n

      The <\/span>only change to the previous join SQL statement is the use of “RIGHT” instead of <\/span>“LEFT”.<\/span><\/p>\n

      Right <\/span>refers to the fact that all elements of the left table in “Employee RIGHT OUTER <\/span>JOIN Entrance” (which is Entrance) will appear in the result. “<\/span><\/p>\n

      Note: <\/span>Some SQL variants will not use “OUTER”.<\/span><\/p>\n

       <\/b><\/span> <\/b><\/span><\/p>\n

      4) All Entrances and Employees (SQL Full Join)<\/b><\/span><\/p>\n

      The <\/span>join below shows all employees and all entrances, including Employees with no <\/span>corresponding entrances (Hanna) or entrances with no corresponding employee <\/span>(the entrance for Employee 44).<\/span><\/p>\n

      \n\n\n\n\n\n\n\n\n\n\n
      \n

      Name<\/b><\/span><\/p>\n<\/td>\n

      		\n

      When<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 08:05:01 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 07:59:12 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      John<\/span><\/p>\n<\/td>\n

      		\n

      6\/14\/2012 07:49:10 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/12\/2012 10:33:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Mark<\/span><\/p>\n<\/td>\n

      		\n

      6\/13\/2012 10:15:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      Hanna<\/span><\/p>\n<\/td>\n

      		\n

       <\/p>\n<\/td>\n<\/tr>\n

      \n

       <\/p>\n<\/td>\n

      		\n

      2\/29\/2012 01:00:00 AM<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

       <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      The SQL Statement looks like:<\/span><\/p>\n

      SELECT   Employee.Name<\/b>, <\/span>Entrance.[When]<\/b><\/span><\/p>\n

      FROM    Employee<\/b><\/span>
      FULL OUTER JOIN<\/i> Entrance<\/b> ON Employee.id = Entrance.EmployeeId<\/b><\/span><\/p>\n

      The <\/span>only change to the previous join the use of “FULL” instead of “RIGHT” or <\/span>“LEFT”.<\/span><\/p>\n

      Full <\/span>refers to the fact that all elements of both join sides (Employee and Entrance <\/span>specified in “Employee FULL OUTER JOIN Entrance”) will appear in the result. <\/span><\/p>\n

      Note: <\/span>Some SQL variants will not use “OUTER”.<\/span><\/p>\n

       <\/p>\n

      Join in Windows PowerShell<\/span><\/h2>\n

      There is no built-in <\/span>cmdlet for joining in Windows PowerShell, so the remainder of this post will be <\/span>about building a cmdlet called Join-Object that performs the four join <\/span>operations in lists of objects in memory. Those objects can come from different <\/span>places, including csv files.<\/span><\/p>\n

      For example, lets create a couple <\/span>of CSV files that contain the sample data we have been using:<\/span><\/p>\n

      @”<\/span>
      Id,Name<\/span>
      1,John<\/span>
      2,Mark<\/span>
      3,Hanna<\/span>
      “@ > c:\\temp\\employee.csv<\/span><\/p>\n

      @”<\/span>
      EmployeeId,When<\/span>
      1,6\/12\/2012 08:05:01 AM<\/span>
      1,6\/13\/2012 07:59:12 AM<\/span>
      1,6\/14\/2012 07:49:10 AM<\/span>
      2,6\/12\/2012 10:33:00 AM<\/span>
      2,6\/13\/2012 10:15:00 AM<\/span>
      44,2\/29\/2012 01:00:00 AM<\/span>
      “@ > c:\\temp\\entrance.csv<\/span><\/p>\n

      $employee = Import-Csv c:\\temp\\employee.csv<\/span>
      $entrance = Import-Csv c:\\temp\\entrance.csv<\/span><\/p>\n

       <\/p>\n

      After running these commands, we <\/span>have:<\/span><\/p>\n

      PS C:\\temp> $employee<\/span><\/p>\n

      Id       Name                                                                          <\/span>
      —       —-                                                                          <\/span>
      1        John                                                                          <\/span>
      2        Mark                                                                          <\/span>
      3        Hanna  <\/span><\/p>\n

       <\/p>\n

      And:<\/span><\/p>\n

      PS C:\\temp> $entrance<\/span><\/p>\n

       <\/p>\n

      EmployeeId        When                 <\/span>
      ———-        —-                 <\/span>
      1                 6\/12\/2012 08:05:01 AM<\/span>
      1                 6\/13\/2012 07:59:12 AM<\/span>
      1                 6\/14\/2012 07:49:10 AM<\/span>
      2                 6\/12\/2012 10:33:00 AM<\/span>
      2                 6\/13\/2012 10:15:00 AM<\/span>
      44                2\/29\/2012 01:00:00 AM<\/span><\/p>\n

      <\/span> <\/p>\n

      I chose CSV because you can find <\/span>data to be joined in CSV files and also because it’s a compact way to show the sample <\/span>data.<\/span><\/p>\n

      Below are 4 calls to the Join-Object <\/span>that produce the equivalent of the 4 types of join.<\/span><\/p>\n

       <\/p>\n

      PS C:\\temp> Join-Object -Left $employee -Right $entrance -Where {$args[0].Id -eq $args[1].EmployeeId} –LeftProperties “Name” –RightProperties “When” -Type OnlyIfInBoth<\/span><\/p>\n

       <\/p>\n

      Name                         When                                   <\/span>
      —-                         —-                                   <\/span>
      John                         6\/12\/2012 08:05:01 AM                <\/span>
      John                         6\/13\/2012 07:59:12 AM       <\/span>
      John                         6\/14\/2012 07:49:10 AM       <\/span>
      Mark                         6\/12\/2012 10:33:00 AM       <\/span>
      Mark                         6\/13\/2012 10:15:00 AM   <\/span>   <\/p>\n

       <\/p>\n

      Join-Object’s first <\/span>two parameters are the left and right lists of objects.<\/span><\/p>\n

      The “Where” <\/span>parameter is the join condition, and it uses $args[0] to represent an item from <\/span>the left list and $args[1] for an item in the right list.<\/span><\/p>\n

      The RightProperties <\/span>and LeftProperties parameters are the names of the properties to appear in the <\/span>output from the left list and right list.<\/span><\/p>\n

      The Type parameter <\/span>is one of four names: AllInLeft, AllInRight, OnlyIfInBoth and AllInBoth. I <\/span>thought those four names were simpler to relate to their functionality than <\/span>SQL’s full<\/i>, inner<\/i> and outer<\/i> names, <\/span>but here is a mapped correspondence:<\/span><\/p>\n

        <\/p>\n

      \n\n\n\n\n\n\n\n
      \n

      Join-Object<\/b><\/span><\/p>\n<\/td>\n

      		\n

      SQL<\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      AllInLeft<\/span><\/p>\n<\/td>\n

      		\n

      Left Outer<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      AllIInRight<\/span><\/p>\n<\/td>\n

      		\n

      Right Outer<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      OnlyIfInBoth<\/span><\/p>\n<\/td>\n

      		\n

      Inner<\/span><\/p>\n<\/td>\n<\/tr>\n

      \n

      AllInBoth<\/span><\/p>\n<\/td>\n

      		\n

      Full Outer<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

       <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span> <\/p>\n

      <\/span>  <\/p>\n

      Here is an example of AllInLeft:  <\/span><\/p>\n

       <\/p>\n

      PS C:\\temp> Join-Object -Left $employee -Right $entrance -Where {$args[0].Id -eq $args[1].EmployeeId} -LeftProperties “Name” -RightProperties “When” -Type AllInLeft<\/span><\/p>\n

       <\/p>\n

      Name                         When                           <\/span>
      —-                         —-                           <\/span>
      John                         6\/12\/2012 08:05:01 AM     <\/span>
      John                         6\/13\/2012 07:59:12 AM     <\/span>
      John                         6\/14\/2012 07:49:10 AM     <\/span>
      Mark                         6\/12\/2012 10:33:00 AM     <\/span>
      Mark                         6\/13\/2012 10:15:00 AM     <\/span>
      Hanna                                                       <\/span><\/p>\n

       <\/p>\n

      Here is the output of AllInRight:<\/span><\/p>\n

      PS C:\\temp> Join-Object -Left $employee -Right $entrance -Where {$args[0].Id -eq $args[1].EmployeeId} -LeftProperties “Name” -RightProperties “When” -Type AllInRight <\/span><\/p>\n

      Name                         When                                                     <\/span>
      —-                         —-                                                     <\/span>
      John                         6\/12\/2012 08:05:01 AM    <\/span>
      John                         6\/13\/2012 07:59:12 AM    <\/span>
      John                         6\/14\/2012 07:49:10 AM    <\/span>
      Mark                         6\/12\/2012 10:33:00 AM    <\/span>
      Mark                         6\/13\/2012 10:15:00 AM    <\/span>
                                   2\/29\/2012 01:00:00 AM   <\/span><\/p>\n

      And finally, the output of AllInBoth:<\/span><\/p>\n

       <\/p>\n

      PS C:\\temp> Join-Object –Left $employee –Right $entrance –Where {$args[0].Id -eq $args[1].EmployeeId} –LeftProperties “Name” –RightProperties “When” -Type AllInBoth <\/span><\/p>\n

      Name                        When                           <\/span>
      —-                        —-                           <\/span>
      John                        6\/12\/2012 08:05:01 AM        <\/span>
      John                        6\/13\/2012 07:59:12 AM        <\/span>
      John                        6\/14\/2012 07:49:10 AM        <\/span>
      Mark                        6\/12\/2012 10:33:00 AM        <\/span>
      Mark                        6\/13\/2012 10:15:00 AM        <\/span>
      Hanna                                                      <\/span>
                                  2\/29\/2012 01:00:00 AM     <\/span><\/p>\n

      Conclusions<\/span><\/h2>\n

      Join <\/span>is an essential tool for data centric problems because, usually, multiple <\/span>tables are necessary.<\/span><\/p>\n

      Data <\/span>centric problems are normally handled by database tools, but if you happen to <\/span>have CSV files with this kind of data, Join-Object might be useful.<\/span><\/p>\n

      <\/span> <\/p>\n

      Lucio Silveira [MSFT]<\/span><\/p>\n

       <\/h2>\n

      The Join-Object Script<\/span><\/h2>\n

      function AddItemProperties($item, $properties, $output)<\/span>
      {<\/span>
          if($item -ne $null)<\/span>
          {<\/span>
              foreach($property in $properties)<\/span>
              {<\/span>
                  $propertyHash =$property -as [hashtable]<\/span>
                  if($propertyHash -ne $null)<\/span>
                  {<\/span>
                      $hashName=$propertyHash[“name”] -as [string]<\/span>
                      if($hashName -eq $null)<\/span>
                      {<\/span>
                          throw “there should be a string Name”  <\/span>
                      }<\/span>
              
                      $expression=$propertyHash[“expression”] -as [scriptblock]<\/span>
                      if($expression -eq $null)<\/span>
                      {<\/span>
                          throw “there should be a ScriptBlock Expression”  <\/span>
                      }<\/span>
              
                      $_=$item<\/span>
                      $expressionValue=& $expression<\/span>
              
                      $output | add-member -MemberType “NoteProperty” -Name $hashName -Value $expressionValue<\/span>
                  }<\/span>
                  else<\/span>
                  {<\/span>
                      # .psobject.Properties allows you to list the properties of any object, also known as “reflection”<\/span>
                      foreach($itemProperty in $item.psobject.Properties)<\/span>
                      {<\/span>
                          if ($itemProperty.Name -like $property)<\/span>
                          {<\/span>
                              $output | add-member -MemberType “NoteProperty” -Name $itemProperty.Name -Value $itemProperty.Value<\/span>
                          }<\/span>
                      }<\/span>
                  }<\/span>
              }<\/span>
          }<\/span>
      }<\/span><\/p>\n

         
      function WriteJoinObjectOutput($leftItem, $rightItem, $leftProperties, $rightProperties, $Type)<\/span>
      {<\/span>
          $output = new-object psobject<\/span><\/p>\n

          if($Type -eq “AllInRight”)<\/span>
          {<\/span>
              # This mix of rightItem with LeftProperties and vice versa is due to<\/span>
              # the switch of Left and Right arguments for AllInRight<\/span>
              AddItemProperties $rightItem $leftProperties $output<\/span>
              AddItemProperties $leftItem $rightProperties $output<\/span>
          }<\/span>
          else<\/span>
          {<\/span>
              AddItemProperties $leftItem $leftProperties $output<\/span>
              AddItemProperties $rightItem $rightProperties $output<\/span>
          }<\/span>
          $output<\/span>
      }<\/span><\/p>\n

      <#<\/span>
      .Synopsis<\/span>
         Joins two lists of objects<\/span>
      .DESCRIPTION<\/span>
         Joins two lists of objects<\/span>
      .EXAMPLE<\/span>
         Join-Object $a $b “Id” (“Name”,”Salary”)<\/span>
      #><\/span>
      function Join-Object<\/span>
      {<\/span>
          [CmdletBinding()]<\/span>
          [OutputType([int])]<\/span>
          Param<\/span>
          (<\/span>
              # List to join with $Right<\/span>
              [Parameter(Mandatory=$true,<\/span>
                         Position=0)]<\/span>
              [object[]]<\/span>
              $Left,<\/span><\/p>\n

              # List to join with $Left<\/span>
              [Parameter(Mandatory=$true,<\/span>
                         Position=1)]<\/span>
              [object[]]<\/span>
              $Right,<\/span><\/p>\n

              # Condition in which an item in the left matches an item in the right<\/span>
              # typically something like: {$args[0].Id -eq $args[1].Id}<\/span>
              [Parameter(Mandatory=$true,<\/span>
                         Position=2)]<\/span>
              [scriptblock]<\/span>
              $Where,<\/span><\/p>\n

              # Properties from $Left we want in the output.<\/span>
              # Each property can:<\/span>
              # – Be a plain property name like “Name”<\/span>
              # – Contain wildcards like “*”<\/span>
              # – Be a hashtable like @{Name=”Product Name”;Expression={$_.Name}}. Name is the output property name<\/span>
              #   and Expression is the property value. The same syntax is available in select-object and it is <\/span>
              #   important for join-object because joined lists could have a property with the same name<\/span>
              [Parameter(Mandatory=$true,<\/span>
                         Position=3)]<\/span>
              [object[]]<\/span>
              $LeftProperties,<\/span><\/p>\n

              # Properties from $Right we want in the output.<\/span>
              # Like LeftProperties, each can be a plain name, wildcard or hashtable. See the LeftProperties comments.<\/span>
              [Parameter(Mandatory=$true,<\/span>
                         Position=4)]<\/span>
              [object[]]<\/span>
              $RightProperties,<\/span><\/p>\n

              # Type of join. <\/span>
              #   AllInLeft will have all elements from Left at least once in the output, and might appear more than once<\/span>
              # if the where clause is true for more than one element in right, Left elements with matches in Right are <\/span>
              # preceded by elements with no matches. This is equivalent to an outer left join (or simply left join) <\/span>
              # SQL statement.<\/span>
              #  AllInRight is similar to AllInLeft.<\/span>
              #  OnlyIfInBoth will cause all elements from Left to be placed in the output, only if there is at least one<\/span>
              # match in Right. This is equivalent to a SQL inner join (or simply join) statement.<\/span>
              #  AllInBoth will have all entries in right and left in the output. Specifically, it will have all entries<\/span>
              # in right with at least one match in left, followed by all entries in Right with no matches in left, <\/span>
              # followed by all entries in Left with no matches in Right.This is equivallent to a SQL full join.<\/span>
              [Parameter(Mandatory=$false,<\/span>
                         Position=5)]<\/span>
              [ValidateSet(“AllInLeft”,”OnlyIfInBoth”,”AllInBoth”, “AllInRight”)]<\/span>
              [string]<\/span>
              $Type=”OnlyIfInBoth”<\/span>
          )<\/span><\/p>\n

          Begin<\/span>
          {<\/span>
              # a list of the matches in right for each object in left<\/span>
              $leftMatchesInRight = new-object System.Collections.ArrayList<\/span><\/p>\n

              # the count for all matches  <\/span>
              $rightMatchesCount = New-Object “object[]” $Right.Count<\/span><\/p>\n

              for($i=0;$i -lt $Right.Count;$i++)<\/span>
              {<\/span>
                  $rightMatchesCount[$i]=0<\/span>
              }<\/span>
          }<\/span><\/p>\n

          Process<\/span>
          {<\/span>
              if($Type -eq “AllInRight”)<\/span>
              {<\/span>
                  # for AllInRight we just switch Left and Right<\/span>
                  $aux = $Left<\/span>
                  $Left = $Right<\/span>
                  $Right = $aux<\/span>
              }<\/span><\/p>\n

              # go over items in $Left and produce the list of matches<\/span>
              foreach($leftItem in $Left)<\/span>
              {<\/span>
                  $leftItemMatchesInRight = new-object System.Collections.ArrayList<\/span>
                  $null = $leftMatchesInRight.Add($leftItemMatchesInRight)<\/span><\/p>\n

                  for($i=0; $i -lt $right.Count;$i++)<\/span>
                  {<\/span>
                      $rightItem=$right[$i]<\/span><\/p>\n

                      if($Type -eq “AllInRight”)<\/span>
                      {<\/span>
                          # For AllInRight, we want $args[0] to refer to the left and $args[1] to refer to right,<\/span>
                          # but since we switched left and right, we have to switch the where arguments<\/span>
                          $whereLeft = $rightItem<\/span>
                          $whereRight = $leftItem<\/span>
                      }<\/span>
                      else<\/span>
                      {<\/span>
                          $whereLeft = $leftItem<\/span>
                          $whereRight = $rightItem<\/span>
                      }<\/span><\/p>\n

                      if(Invoke-Command -ScriptBlock $where -ArgumentList $whereLeft,$whereRight)<\/span>
                      {<\/span>
                          $null = $leftItemMatchesInRight.Add($rightItem)<\/span>
                          $rightMatchesCount[$i]++<\/span>
                      }<\/span>
                 
                  }<\/span>
              }<\/span><\/p>\n

              # go over the list of matches and produce output<\/span>
              for($i=0; $i -lt $left.Count;$i++)<\/span>
              {<\/span>
                  $leftItemMatchesInRight=$leftMatchesInRight[$i]<\/span>
                  $leftItem=$left[$i]<\/span>
                                    
                  if($leftItemMatchesInRight.Count -eq 0)<\/span>
                  {<\/span>
                      if($Type -ne “OnlyIfInBoth”)<\/span>
                      {<\/span>
                          WriteJoinObjectOutput $leftItem  $null  $LeftProperties  $RightProperties $Type<\/span>
                      }<\/span><\/p>\n

                      continue<\/span>
                  }<\/span><\/p>\n

                  foreach($leftItemMatchInRight in $leftItemMatchesInRight)<\/span>
                  {<\/span>
                      WriteJoinObjectOutput $leftItem $leftItemMatchInRight  $LeftProperties  $RightProperties $Type<\/span>
                  }<\/span>
              }<\/span>
          }<\/span><\/p>\n

          End<\/span>
          {<\/span>
              #produce final output for members of right with no matches for the AllInBoth option<\/span>
              if($Type -eq “AllInBoth”)<\/span>
              {<\/span>
                  for($i=0; $i -lt $right.Count;$i++)<\/span>
                  {<\/span>
                      $rightMatchCount=$rightMatchesCount[$i]<\/span>
                      if($rightMatchCount -eq 0)<\/span>
                      {<\/span>
                          $rightItem=$Right[$i]<\/span>
                          WriteJoinObjectOutput $null $rightItem $LeftProperties $RightProperties $Type<\/span>
                      }<\/span>
                  }<\/span>
              }<\/span>
          }<\/span>
      }<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

      Problem: How do I join two lists of objects into a single list? Or the equivalent: How do I join two CSV files into a single one? Note that joining makes sense only when the two lists or CSV files have something in common.   Why do we need Join? If you know why you […]<\/p>\n","protected":false},"author":600,"featured_media":13641,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2261","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell"],"acf":[],"blog_post_summary":"

      Problem: How do I join two lists of objects into a single list? Or the equivalent: How do I join two CSV files into a single one? Note that joining makes sense only when the two lists or CSV files have something in common.   Why do we need Join? If you know why you […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/2261","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/users\/600"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/comments?post=2261"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/2261\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media\/13641"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media?parent=2261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/categories?post=2261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/tags?post=2261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}