Exact and relational joins
This guide covers exact and relational joins in Deephaven. Both kinds of join combine data from two tables by matching values in one or more key columns. This guide calls the table that receives data the left table and the table that supplies it the right table.
- An exact join adds columns from the right table to every row of the left table, using at most one matching right row per key. The result has the same rows as the left table. These table operations perform an exact join:
- A relational join pairs each left row with every matching right row, so one key can produce several result rows. Depending on the operation, the result can also include rows that have no match. These table operations perform a relational join:
To join three or more tables on matching key values in one operation, use multi_join, described in Join three or more tables. The key columns can have different names in each table.
Exact and relational joins match key values exactly. To match on the nearest value or on a range of values, see Inexact, time-series, and range joins.
Which method should you use?
Answer these questions to choose a join method:
- What should happen when the right table has more than one match for a key?
exact_joinraises an error.natural_joinraises an error by default. It can instead keep the first or last matching row.join,left_outer_join, andfull_outer_joininclude a result row for every match.
- What should happen to a left-table row with no match?
exact_joinraises an error.natural_join,left_outer_join, andfull_outer_joinkeep the row and fill the right table's columns with null values.joinleaves the row out of the result.
- Should the result include right-table rows that match nothing in the left table?
- Only
full_outer_joinincludes them, with null values in the left table's columns.
- Only
- Are you joining three or more tables on the same keys, even if the key column names differ, with at most one row per key in each table?
- Use
multi_join.
- Use
The following flowchart walks through the same choices, including the inexact joins described in Inexact, time-series, and range joins.
Syntax
join, exact_join, and natural_join are methods of the left table:
left_outer_join and full_outer_join are functions in the deephaven.experimental.outer_joins module that take both tables as arguments:
Besides the two tables, these operations take two main arguments. Each is a column name or expression, or a list of them:
on: The key columns to match. Required forexact_joinandnatural_join. Optional forjoinand the outer joins, which pair every left row with every right row whenonis omitted.joins(optional): The columns from the right table to add to the left table. If omitted, the join adds every right table column except those whose names match a left table column inon. A right table match column with a different name from its left table match column, such asDeptIDin"DeptNumber = DeptID", is added.
A key column can be of any data type, but each pair of matched columns in the left and right tables must have the same data type. Custom objects used as keys must implement consistent equality and hash codes.
Match columns with different names
The key columns in two tables often have different names. The syntax below joins left_table and right_table on ColumnToMatchLeft and ColumnToMatchRight:
Multiple match columns
To join tables on more than one key column, list each one in on:
Rename joined columns
If a column added from the right table has the same name as a column in the left table, the join fails with a name conflict. To avoid this, rename the column in the joins argument. The following example adds the right table's OldColumnName column to the result as NewColumnName:
Example tables
The examples in the Exact joins and Relational joins sections use two tables. employees lists employees and the ID of the department each one works in. Rogers works in department 36, which doesn't exist, and DelaCruz has no department. departments lists departments by ID. No employee works in Marketing (department 35).
Exact joins
An exact join keeps every row of the left table and appends columns from the matching row of the right table. exact_join and natural_join differ in how they handle a left-table row with no match, and in whether they can keep one of several matching right rows.
exact_join
exact_join requires every row in the left table to have exactly one matching row in the right table. The operation fails if a left-table key has no match or more than one match in the right table. The operation ignores right-table keys that have no match in the left table.
Calling exact_join on the whole employees table fails because Rogers and DelaCruz have no matching department. This example first keeps only the employees whose department exists, and then adds the department columns:
natural_join
natural_join allows left-table rows that have no match in the right table. The appended columns in those rows are null. In this example, Rogers and DelaCruz get null department columns:
By default, natural_join fails if a left-table row matches more than one right-table row. Duplicate right-table keys that no left-table row matches don't cause an error. To keep one of the matching rows instead, set type to NaturalJoinType.FIRST_MATCH or NaturalJoinType.LAST_MATCH. The following example swaps the tables, using departments as the left table, and adds the first matching employee to each department. Departments 33 and 34 each have two employees, so the default join type would fail. Marketing has no employees, so its employee columns are null:
Relational joins
Unlike exact joins, which use at most one matching right row per key, relational joins keep every matching right row. Each left row appears in the result once for every matching right row. The three relational joins differ in which unmatched rows they keep. The examples in this section use departments as the left table and employees as the right table, so a department with several employees produces several result rows.
join
The output table from a join contains a row for every pair of matching left and right rows. The result leaves out rows that have no match in the other table. In this example, the Engineering and Clerical departments each appear twice, once per employee. Marketing doesn't appear because no employee works there:
Tip
join produces a result row for every matching pair of left and right table rows and reserves space in each result row key for the matching right table rows. This makes it slower than natural_join. Its result can also be much larger than either input, which costs more to maintain on ticking tables. Use natural_join when each left row needs at most one matching right row. Use join when each left row needs every matching right row.
left_outer_join
Note
This table operation is currently experimental. The API may change in the future.
The output table from a left_outer_join contains every row that join would produce, plus each left-table row that has no match, with null values in the right table's columns. In this example, Marketing appears with null employee columns:
full_outer_join
Note
This table operation is currently experimental. The API may change in the future.
The output table from a full_outer_join contains every row that left_outer_join would produce, plus each right-table row that has no match, with null values in the left table's columns. In this example, Marketing appears with null employee columns, and Rogers and DelaCruz appear with null department names:
Join three or more tables
multi_join joins any number of tables on a common set of key columns in a single operation. The result has one row for each distinct key found in any input table. Each input table adds its columns to that row. Each input table can have at most one row per key, and multi_join fails if an input has duplicate keys. An input table with no row for a key contributes null values.
multi_join returns a MultiJoinTable object rather than a table. To get the result table, use its table property.
There are two ways to call multi_join:
- With constituent tables: Pass the tables to join directly. These input tables are called constituent tables. Every constituent table must use the same key column names, and the result includes every non-key column from every constituent table.
- With
MultiJoinInputobjects: Pass a list ofMultiJoinInputobjects. EachMultiJoinInputspecifies one table, the mapping from its key columns to the result's key columns, and the columns to add from it. The columns to add are optional.
With constituent tables
To use constituent tables, pass them as a list in input, and pass a key column name or a list of key column names in on:
The following example joins three tables of letter grades for students in grades 5, 6, and 7. Not every student appears in every table, so some grades in the result are null:
With MultiJoinInput objects
Use MultiJoinInput objects when the key columns have different names in different tables, or when you want only some of a table's columns in the result. Create one MultiJoinInput per table, and then pass the list to multi_join without the on argument:
The following example adds each student's club to the grades from the previous example. The clubs table names its key column Student instead of Name, and the result includes only its Club column: