A JOIN statement used in Realtime Compute has the same semantics as a traditional JOIN statement for batch processing. Both statements are used to join two tables. However, a Realtime Compute JOIN statement joins two dynamic tables and the join results are updated dynamically to ensure that the final result is consistent with the batch processing result.

Syntax

tableReference [, tableReference ]* | tableexpression
[ LEFT ] JOIN tableexpression [ joinCondition ];
Note
  • An equijoin is supported, whereas a non-equijoin is not supported.
  • Only INNER JOIN and LEFT OUTER JOIN are supported.

Example 1

  • Test data

    Orders:

    rowtime productId orderId units
    10:17:00 30 5 4
    10:17:05 10 6 1
    10:18:05 20 7 2
    10:18:07 30 8 20
    11:02:00 10 9 6
    11:04:00 10 10 1
    11:09:30 40 11 12
    11:24:11 10 12 4

    Products:

    productId name unitPrice
    30 Cheese 17
    10 Beer 0.25
    20 Wine 6
    30 Cheese 17
    10 Beer 0.25
    10 Beer 0.25
    40 Bread 100
    10 Beer 0.25
  • Test statement
      SELECT o.rowtime, o.productId, o.orderId, o.units,p.name, p.unitPrice
  FROM Orders AS o
  JOIN Products AS p
  ON o.productId = p.productId;
  • Test result
    rowtime productId orderId units name unitPrice
    10:17:00 30 5 4 Cheese 17
    10:17:05 10 6 1 Beer 0.25
    10:18:05 20 7 2 Wine 6
    10:18:07 30 8 20 Cheese 17
    11:02:00 10 9 6 Beer 0.25
    11:04:00 10 10 1 Beer 0.25
    11:09:30 40 11 12 Bread 100
    11:24:11 10 12 4 Beer 0.25

Example 2

  • Test data

    datahub_stream1:

    a (BIGINT) b (BIGINT) c (VARCHAR)
    0 10 test11
    1 10 test21

    datahub_stream2:

    a (BIGINT) b (BIGINT) c (VARCHAR)
    0 10 test11
    1 10 test21
    0 10 test31
    1 10 test41
  • Test statement
    SELECT s1.c,s2.c 
FROM datahub_stream1 AS s1
JOIN datahub_stream2 AS s2 
ON s1.a =s2.a
WHERE s1.a = 0;
  • Test result
    s1_c (VARCHAR) s2_c (VARCHAR)
    test11 test11
    test11 test31