You can specify JOIN clauses in SQL statements to join multiple tables based on the fields that are shared by the tables. Log Service allows you to join data that is stored in different Logstores. You can also join data that is stored in a Logstore with data that is stored in a MySQL database or with data that is stored in an Object Storage Service (OSS) bucket. This topic describes the syntax of JOIN clauses and provides examples on how to use JOIN clauses.

Syntax

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

Log Service allows you to use INNER JOIN clauses, LEFT JOIN clauses, RIGHT JOIN clauses, and OUTER JOIN clauses in SELECT statements. For more information, see JOIN.

JOIN syntax Description
INNER JOIN Returns only the matching rows that meet the conditions specified in the SELECT statement between two tables.
LEFT JOIN Returns all rows that meet the conditions specified in the SELECT statement from the left table (table1) even if no matching rows exist in the right table (table2).
RIGHT JOIN Returns all rows that meet the conditions specified in the SELECT statement from the right table (table2) even if no matching rows exist in the left table (table1).
FULL OUTER JOIN Returns the rows that meet the conditions specified in the SELECT statement if a table contains a matching row.

Parameters

Parameter Description
key A log field or an expression. The value of this parameter can be of an arbitrary data type.
table table1 is a Logstore and table2 can be a Logstore, a MySQL database, or an OSS bucket. For more information, see Associate Log Service with a MySQL database and Associate Log Service with an OSS bucket.

Examples

A Logstore named internal-diagnostic_log is used to record the logs that include information, such as the consumption latency, alerts, and log collection for each Logstore in a project. A Logstore named internal-operation_log is used to record the operation logs of all resources in the project. You can use a JOIN clause to query log data from the two Logstores and obtain the information about the consumer groups, consumption latency, and request methods for each Logstore in the project.

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
  • Query and analysis result

    In this example, 1,328 rows of data that meet the specified conditions are returned.

    inner join

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
  • Query and analysis result

    In this example, 1,328 rows of data in the internal-diagnostic_log Logstore are returned.

    LEFT JOIN

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
  • Query and analysis result

    In this example, 1,757 rows of data in the internal-operation_log Logstore are returned.

    RIGHT JOIN

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
  • Query and analysis result

    In this example, 1,757 rows of data that meet the specified conditions are returned.

    FULL OUTER JOIN