Outer Joins

Another way to get missing data is when performing a left, right or full outer join. The missing value is used to fill out unmatched tuples, for example:

    {[$:name, #:dept_id, &:salary]}: employees := {
      ["Gosia Wrzesinska", 1, 123.50],
      ["Daniela Gavidia", 1, 234.50],
      ["Matt Dobson", 2, 333.40],
      ["Jan-Mark Wams", 3, 111.20]
    };

    {[#:dept_id, $:dept_name]}: departments := {
      [1, "Software development"],
      [2, "Hardware maintenance"],
      [3, "Entertainment"],
      [4, "HR"]                    // HR has no employees.
    };

    `print departments =>< employees;  // Left outer join.

    // Output:
    // dept_id|       dept_name      |       name       |salary
    // -------|----------------------|------------------|------
    //    1   |"Software development"| "Daniela Gavidia"| 234.5
    //    1   |"Software development"|"Gosia Wrzesinska"| 123.5
    //    2   |"Hardware maintenance"|   "Matt Dobson"  | 333.4
    //    3   |    "Entertainment"   |  "Jan-Mark Wams" | 111.2
    //    4   |         "HR"         |        ""        |   0
         

In this example, the "HR" department doesn't have any employees, so the tuple in the result of the left outer join was filled out with default values for name string and salary real. Since the missing value for real is 0.0, taking the total of salaries per department would deliver correct results, as shown in the example below:

    `print fold [.dept_name, /sum(.salary)]
             departments =>< employees;

    // Output:
    //           #1          |  #2
    // ----------------------|-----
    //     "Entertainment"   |111.2
    //          "HR"         | 0.0
    // "Hardware maintenance"|333.4
    // "Software development"| 358