JOIN clause - query multiple Logstores, MySQL, and OSS - Simple Log Service

Syntax

SELECT table.key
FROM table1
INNER|LEFT|RIGHT|FULL OUTER JOIN table2
ON table1.key=table2.key

SLS supports INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN in SELECT statements, as described in the MySQL JOIN reference.

JOIN syntax	Description
INNER JOIN	Returns only rows that match the join condition in both tables.
LEFT JOIN	Returns all rows from the left table (table1), even if no matching rows exist in the right table (table2).
RIGHT JOIN	Returns all rows from the right table (table2), even if no matching rows exist in the left table (table1).
FULL JOIN	Returns rows if either table contains a matching row.

Parameters

Parameter	Description
key	A log field or expression. Supports any data type.
table	table1 is a Logstore. table2 can be a Logstore, MySQL database, or OSS bucket. Associate Simple Log Service with a MySQL database. Associate Simple Log Service with an OSS bucket.

Examples

The following examples join two Logstores in the same project: internal-diagnostic_log (consumption latency, alerts, and collection data) and internal-operation_log (operation logs for project resources). The queries retrieve consumer groups, latency, and request methods.

Example 1: INNER JOIN

Query statement

* |
SELECT
  "internal-diagnostic_log".consumer_group,
  "internal-diagnostic_log".logstore,
  "internal-operation_log".Latency,
  "internal-operation_log".Method
FROM  "internal-diagnostic_log"
  INNER JOIN "internal-operation_log" ON "internal-diagnostic_log".logstore = "internal-operation_log".logstore
LIMIT
  10000

Results

Returns 1,328 matching rows.

The result includes four columns: consumer_group, logstore, Latency, and Method. Sample values: consumer_group etl-cf43c82162ac3ca60a906ebec4c6b87b, logstore website_log, Method PullData, Latency 513, 591, and 465.

Example 2: LEFT JOIN

Query statement

* |
SELECT
  "internal-diagnostic_log".consumer_group,
  "internal-diagnostic_log".logstore,
  "internal-operation_log".Latency,
  "internal-operation_log".Method
FROM  "internal-diagnostic_log"
  LEFT JOIN "internal-operation_log" ON "internal-diagnostic_log".logstore = "internal-operation_log".logstore
LIMIT
  10000

Results

Returns all 1,328 rows from the internal-diagnostic_log Logstore.

The result includes four columns: consumer_group, logstore, Latency, and Method. Method values include ConsumerGroupHeartBeat and PullData.

Example 3: RIGHT JOIN

Query statement

* |
SELECT
  "internal-diagnostic_log".consumer_group,
  "internal-diagnostic_log".logstore,
  "internal-operation_log".Latency,
  "internal-operation_log".Method
FROM  "internal-diagnostic_log"
  RIGHT JOIN "internal-operation_log" ON "internal-diagnostic_log".logstore = "internal-operation_log".logstore
LIMIT
  10000

Results

Returns all 1,757 rows from the internal-operation_log Logstore.

The result contains four columns: consumer_group, logstore, Latency, and Method. Null values in consumer_group and logstore indicate no matching rows in the left table. Method is PostLogStoreLogs, and Latency values vary.

Example 4: FULL OUTER JOIN

Query statement

* |
SELECT
  "internal-diagnostic_log".consumer_group,
  "internal-diagnostic_log".logstore,
  "internal-operation_log".Latency,
  "internal-operation_log".Method
FROM  "internal-diagnostic_log"
  FULL OUTER JOIN "internal-operation_log" ON "internal-diagnostic_log".logstore = "internal-operation_log".logstore
LIMIT
  10000

Results

Returns 1,757 rows.

The result contains four columns: consumer_group, logstore, Latency, and Method. Includes both matched rows and rows with null consumer_group and logstore values. Non-matching rows show Method as PostLogStoreLogs.