tencent cloud

Cloud Log Service

DocumentationCloud Log ServiceOperation GuideSearch and Analysis (Log Topic)Statistical Analysis (SQL)SQL SyntaxJOIN Syntax

JOIN Syntax

Download
Focus Mode
Font Size
Last updated: 2026-05-06 19:02:56
The JOIN syntax is used to join multiple tables and supports the following usage methods:
Join multiple query results in the same log topic.
Connect to CSV files and MySQL databases in Cloud Object Storage. For details, see Associating with External Data.
Connect multiple log topics. For details, see Cross-Topic Association Analysis.

Syntax Format

* | SELECT [column name(KEY)] FROM table1 JOIN_TYPE table2 ON table1.key=table2.key
Where:
When the column name (KEY) is the same in multiple tables, the table name or alias should be specified.
JOIN_TYPE supports the following types (the content in [ ] can be omitted):
[ INNER ] JOIN: Return results that exist in both tables.
LEFT [ OUTER ] JOIN: Return all SELECT results from the left table (table1) even if there is no matching data in the right table (table2).
RIGHT [ OUTER ] JOIN: Return all SELECT results from the right table (table2) even if there is no matching data in the left table (table1).
FULL [ OUTER ] JOIN: Return all SELECT results that meet the conditions as long as matching data exists in one table.
CROSS JOIN: JOIN_CONDITION does not need to be specified (namely, ON table1.key=table2.key), and the Cartesian product of two tables (all possible combinations) is returned.
JOIN_CONDITION, namely, ON table1.key=table2.key. You can also specify filtering conditions at the same time, such as ON (table1.key=table2.key and table2.key='xxx'), which will filter the data in the original tables according to the specified conditions before the JOIN query.

Syntax Example

LEFT JOIN: Query ip, url, and user_id from the log data (log), and query name and gender from the userinfo table based on user_id.
* | 
select 
  log.ip, 
  log.url, 
  log.user_id, 
  userinfo.Name, 
  userinfo.Gender 
from 
  log 
  left join userinfo on log.user_id = userinfo.id 
limit 
  10000
Return result:

INNER JOIN: Count the number of error logs (errorCount) and the total number of log entries (allCount) for each URL in the current log topic, and then join them together. The instance also demonstrates the method of using nested subqueries together with JOIN syntax.
* | 
select 
  errorCount, 
  allCount, 
  table1.url as url 
from 
  (
    select 
      count(*) as errorCount, 
      url 
    where 
      status_code != 200 
    group by 
      url
  ) as table1 
  inner join (
    select 
      count(*) as allCount, 
      url 
    group by 
      url
  ) as table2 on table1.url = table2.url
Return result:




Previous Topic: Nested SubqueryNext Topic: UNION Syntax

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback