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 MultiJoinFactory.of, 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:

The following flowchart walks through the same choices, including the inexact joins described in Inexact, time-series, and range joins.

Syntax

join, exactJoin, and naturalJoin are methods of the left table:

leftOuterJoin and fullOuterJoin are static methods of the OuterJoinTools class that take both tables as arguments:

Besides the two tables, these operations take two main arguments. Each is a String of comma-separated column names or expressions:

  • columnsToMatch: The key columns to match. Required for exactJoin, naturalJoin, and the outer joins. join can omit it, as in leftTable.join(rightTable), which pairs every left row with every right row. For leftOuterJoin and fullOuterJoin, an empty string ("") has the same effect.
  • columnsToAdd (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 in columnsToMatch. A right table match column with a different name from its left table match column, such as DeptID in "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 equals and hashCode methods.

Match columns with different names

The key columns in two tables often have different names. The syntax below joins leftTable and rightTable on ColumnToMatchLeft and ColumnToMatchRight:

Multiple match columns

To join tables on more than one key column, list them all in a single comma-separated String:

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 columnsToAdd 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. exactJoin and naturalJoin differ in how they handle a left-table row with no match, and in whether they can keep one of several matching right rows.

exactJoin

exactJoin 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 exactJoin 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:

naturalJoin

naturalJoin 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, naturalJoin 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, pass NaturalJoinType.FIRST_MATCH or NaturalJoinType.LAST_MATCH as the last argument. 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 naturalJoin. Its result can also be much larger than either input, which costs more to maintain on ticking tables. Use naturalJoin when each left row needs at most one matching right row. Use join when each left row needs every matching right row.

leftOuterJoin

Note

This table operation is currently experimental. The API may change in the future.

The output table from a leftOuterJoin 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:

fullOuterJoin

Note

This table operation is currently experimental. The API may change in the future.

The output table from a fullOuterJoin contains every row that leftOuterJoin 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

MultiJoinFactory.of 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 MultiJoinFactory.of fails if an input has duplicate keys. An input table with no row for a key contributes null values.

MultiJoinFactory.of returns a MultiJoinTable object rather than a table. To get the result table, call its table method.

There are two ways to call MultiJoinFactory.of:

  • 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 MultiJoinInput objects: Pass MultiJoinInput objects. Each MultiJoinInput specifies 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 a String of comma-separated key column names, such as "Key1, Key2", followed by the tables:

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 with MultiJoinInput.of, and then pass them all to MultiJoinFactory.of:

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: