JOIN statements in Realtime Compute for Apache Flink have the same semantic meanings as those for batch processing. The two types of JOIN statements allow you to join two tables. The difference is that each JOIN statement in Realtime Compute for Apache Flink joins two dynamic tables. The join results are dynamically updated to ensure that the final results are the same as the corresponding results of batch processing.

Syntax

tableReference [, tableReference ]* | tableexpression
[ LEFT ] JOIN tableexpression [ joinCondition ];
  • tableReference: specifies the table name.
  • tableexpression: specifies the expression.
  • joinCondition: specifies the join condition.
Notice
  • Only EQUI JOIN operations are supported.
  • Only INNER JOIN and LEFT OUTER JOIN operations are supported.

Example 1: Join the Orders table and the Products table

  • Test data
    Table 1. 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
    Table 2. 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
    o.rowtime o.productId o.orderId o.units p.name p.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: Join the datahub_stream1 table and the datahub_stream2 table

  • Test data
    Table 3. datahub_stream1
    a (BIGINT) b (BIGINT) c (VARCHAR)
    0 10 test11
    1 10 test21
    Table 4. 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