JOIN

JOIN lets you combine two more more table expressions into a single table expression.

Conceptual Framework 

Much like an RDBMS, Materialize can join together any two table expressions (in our case, either sources or views) into a single table expression.

Materialize’s support for JOIN is radically simplified from most streaming platforms, and generally provides unrestricted access to JOINs.

Syntax 

join_expr 

select_pred CROSS NATURAL join_type JOIN table_ref join_type JOIN table_ref USING ( col_ref , ) ON expression select_post

join_type 

FULL LEFT RIGHT OUTER INNER
Field Use
select_pred The predicating SELECT clauses you want to use, e.g. SELECT col_ref FROM table_ref.... The table_ref from the select_pred is the left-hand table.
NATURAL Join table expressions on all columns with the same names in both tables. This is similar to the USING clause naming all identically named columns in both tables.
join_type The type of JOIN you want to use (INNER is implied default).
table_ref The table expression you want to join, i.e. the right-hand table.
USING ( col_ref… ) If the join condition does not require table-level qualification (i.e. joining tables on columns with the same name), the columns to join the tables on. For example, USING (customer_id).
ON expression The condition on which to join the tables. For example ON purchase.customer_id = customer.id.
select_pred The remaining SELECT clauses you want to use, e.g. ...WHERE expr GROUP BY col_ref HAVING expr.

Note: It’s possible to join together table expressions as inner joins without using this clause whatsoever, e.g. SELECT cols... FROM t1, t2 WHERE t1.x = t2.x GROUP BY cols...

Details 

Unlike most other streaming platforms, JOINs in Materialize have very few, if any, restrictions. For example, Materialize:

Instead, JOINs work over the available history of both streams, which ultimately provides an experience more similar to an RDBMS than other streaming platforms.

Examples 

For these examples, we’ll use a small data set:

Employees

 id |  name
----+--------
  1 | Frank
  2 | Arjun
  3 | Nikhil
  4 | Cuong

Managers

 id | name  | manages
----+-------+---------
  1 | Arjun |       4
  2 | Cuong |       3
  3 | Frank |

In this table:

Inner Join 

Inner joins return all tuples from both tables where the join condition is valid.

inner join diagram

SELECT
  employees."name" AS employee,
  managers."name" AS manager
FROM employees
INNER JOIN managers
  ON employees.id = managers.manages;
 employee | manager
----------+---------
 Cuong    | Arjun
 Nikhil   | Cuong

Left Outer Join 

Left outer joins (also known as left joins) return all tuples from the left-hand-side table, and all tuples from the right-hand-side table that match the join condition. Tuples on from the left-hand table that are not joined with a tuple from the right-hand table contain NULL wherever the right-hand table is referenced.

left outer join diagram

SELECT
  employees."name" AS employee,
  managers."name" AS manager
FROM employees
LEFT OUTER JOIN managers
  ON employees.id = managers.manages;
 employee | manager
----------+---------
 Cuong    | Arjun
 Nikhil   | Cuong
 Arjun    |
 Frank    |

Right Outer Join 

Right outer joins (also known as right joins) are simply the right-hand-side equivalent of left outer joins.

Right outer joins (return all tuples from the right-hand-side table, and all tuples from the left-hand-side table that match the join condition. Tuples on from the right-hand table that are not joined with a tuple from the left-hand table contain NULL wherever the left-hand table is referenced.

right outer join diagram

SELECT
  employees."name" AS employee,
  managers."name" AS manager
FROM employees
RIGHT OUTER JOIN managers
  ON employees.id = managers.manages;
 employee | manager
----------+---------
 Cuong    | Arjun
 Nikhil   | Cuong
          | Frank

Full Outer Join 

Full outer joins perform both a left outer join and a right outer join. They return all tuples from both tables, and join them together where the join conditions are met.

Tuples that are not joined with the other table contain NULL wherever the other table is referenced.

full outer join diagram

SELECT
  employees."name" AS employee,
  managers."name" AS manager
FROM employees
FULL OUTER JOIN managers
  ON employees.id = managers.manages;
 employee | manager
----------+---------
 Cuong    | Arjun
 Nikhil   | Cuong
          | Frank
 Arjun    |
 Frank    |

Cross Join 

Cross joins return the Cartesian product of the two tables, i.e. all combinations of tuples from the left-hand table combined with tuples from the right-hand table.

cross join diagram

Our example dataset doesn’t have a meaningful cross-join query, but the above diagram shows how cross joins form the Cartesian product.