Updated on 2024-09-19 GMT+08:00

JOIN Clause

The JOIN clause queries data in two or more tables. This section describes how to use the JOIN clause.

Syntax

select key 
from t1
LEFT|RIGHT|INNER JOIN t2
on t1.key=t2.key

Currently, LTS supports LEFT JOIN, RIGHT JOIN, and INNER JOIN.

Table 1

JOIN Mode

Description

LEFT JOIN

Join the right table (t2) based on the result of the left table (t1).
NOTE:

If the table name contains only digits, add double quotation marks to the table name to convert it into a string. For example, if the table name is 123, enter "123" in the JOIN statement.

RIGHT JOIN

Join the left table (t1) based on the result of the right table (t2).

NOTE:

If the table name contains only digits, add double quotation marks to the table name to convert it into a string. For example, if the table name is 123, enter "123" in the JOIN statement.

INNER JOIN

Intersection data of the results (elb1 and elb2) of two tables

Examples

There are two tables. access indicates the host ingestion metrics, including path, latency, and status code. host indicates the host metrics, including CPU and memory. You can use JOIN to associate ingestion and host metrics and view different dimensions of metrics of the same host.
  • LEFT JOIN
    1. Query statements
      SELECT
      	"access".__time,
      	"access".host_ip,
      	"access".cost,
      	"host".cpu,
      	"host".memory
      FROM
      	log "access"
      	LEFT JOIN (select memory,cpu,host_ip from log) host ON "access".host_ip = "host".host_ip
    2. 60 data records returned
  • RIGHT JOIN
    1. Query statements
      SELECT
      	"access".__time,
      	"host".host_ip,
      	"access".cost,
      	"host".cpu,
      	"host".memory
      FROM
      	log "access"
      	RIGHT  JOIN (select memory,cpu,host_ip from log) host ON "access".host_ip = "host".host_ip
    2. 60 data records returned
  • INNER JOIN
    1. Query statements
      SELECT
      	"access".__time,
      	"host".host_ip,
      	"access".cost,
      	"host".cpu,
      	"host".memory
      FROM
      	log "access"
      	INNER  JOIN (select memory,cpu,host_ip from log) host ON "access".host_ip = "host".host_ip
    2. 45 data records returned