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

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

60 data records returned

RIGHT JOIN

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

60 data records returned

INNER JOIN

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