Documentation

JOIN clause

Use the JOIN clause to join data from different tables together based on logical relationships.

Syntax

SELECT_clause
FROM <left_join_items>
[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]] JOIN <right_join_items>
ON <join_condition>
[WHERE_clause]
[GROUP_BY_clause]
[HAVING_clause]
[ORDER_BY_clause]

Arguments

  • left_join_items: One or more tables specified in the FROM clause that represent the left side of the join.
  • right_join_items: One or more tables specified in the JOIN clause that represent the right side of the join.
  • join_condition: A predicate expression in the ON clause that uses the = (equal to) comparison operator to compare column values from the left side of the join to column values on the right side of the join. Rows with values that match the defined predicate are joined using the specified join type.

If both sides of the join include columns with the same name, you need to use the fully-qualified reference to prevent ambiguity. A fully-qualified reference uses dot notation to reference both the table name and the column name–for example: table_name.column_name

Join types

The following joins types are supported:

Join sample tables

The examples below illustrate join methods using the following tables:

prod_line
timestationproduced
2022-01-01T08:00:00ZB126
2022-01-01T09:00:00ZB154
2022-01-01T10:00:00ZB156
2022-01-01T11:00:00ZB1
2022-01-01T12:00:00ZB182
errors
timestationlevelmessage
2022-01-01T10:00:00ZB1warnMaintenance required
2022-01-01T11:00:00ZB1critStation offline

INNER JOIN

Inner joins combine rows from tables on the left and right side of the join based on common column values defined in the ON clause. Rows that don’t have matching column values are not included in the output table.

Inner join example

SELECT
  *
FROM
  prod_line
RIGHT JOIN errors ON
  prod_line.time = errors.time
  AND prod_line.station = errors.station
ORDER BY
  prod_line.time
Inner join results
timestationproducedtimestationlevelmessage
2022-01-01T10:00:00ZB1562022-01-01T10:00:00ZB1warnMaintenance required
2022-01-01T11:00:00ZB12022-01-01T11:00:00ZB1critStation offline

LEFT [OUTER] JOIN

A left outer join returns all rows from the left side of the join and only returns data from the right side of the join in rows with matching column values defined in the ON clause.

Left outer join example

SELECT
  *
FROM
  prod_line
LEFT JOIN errors ON
  prod_line.time = errors.time
  AND prod_line.station = errors.station
ORDER BY
  prod_line.time
Left outer join results
timestationproducedtimestationlevelmessage
2022-01-01T08:00:00ZB126
2022-01-01T09:00:00ZB154
2022-01-01T10:00:00ZB1562022-01-01T10:00:00ZB1warnMaintenance required
2022-01-01T11:00:00ZB12022-01-01T11:00:00ZB1critStation offline
2022-01-01T12:00:00ZB182

RIGHT [OUTER] JOIN

A right outer join returns all rows from the right side of the join and only returns data from the left side of the join in rows with matching column values defined in the ON clause.

Right outer join example

SELECT
  *
FROM
  prod_line
RIGHT JOIN errors ON
  prod_line.time = errors.time
  AND prod_line.station = errors.station
ORDER BY
  prod_line.time
Right outer join results
timestationproducedtimestationlevelmessage
2022-01-01T10:00:00ZB1562022-01-01T10:00:00ZB1warnMaintenance required
2022-01-01T11:00:00ZB12022-01-01T11:00:00ZB1critStation offline

FULL [OUTER] JOIN

A full outer join returns all data from the left and right sides of the join and combines rows with matching column values defined in the ON clause. Data that is not available on each respective side of the join is NULL.

Full outer join example

SELECT
  *
FROM
  prod_line
FULL JOIN errors ON
  prod_line.time = errors.time
  AND prod_line.station = errors.station
ORDER BY
  time
Full outer join results
timestationproducedtimestationlevelmessage
2022-01-01T08:00:00ZB126
2022-01-01T09:00:00ZB154
2022-01-01T10:00:00ZB1562022-01-01T10:00:00ZB1warnMaintenance required
2022-01-01T11:00:00ZB12022-01-01T11:00:00ZB1critStation offline
2022-01-01T12:00:00ZB182

Troubleshoot joins

Ambiguous reference to unqualified field

If a column exists on both sides of the join and is used in in the SELECT, ON, WHERE, HAVING, GROUP BY, or ORDER BY clause, you must use a fully-qualified reference. For example, if both sides of the join have a time column and you want to explicitly select a time column, you must specifiy which side of the join to use the time column from:

SELECT
  prod_line.time,
  produced,
  message,
FROM
  prod_line
INNER JOIN errors ON
  -- ...

Was this page helpful?

Thank you for your feedback!


The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Read more

InfluxDB v3 enhancements and InfluxDB Clustered is now generally available

New capabilities, including faster query performance and management tooling advance the InfluxDB v3 product line. InfluxDB Clustered is now generally available.

InfluxDB v3 performance and features

The InfluxDB v3 product line has seen significant enhancements in query performance and has made new management tooling available. These enhancements include an operational dashboard to monitor the health of your InfluxDB cluster, single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management APIs for tokens and databases.

Learn about the new v3 enhancements


InfluxDB Clustered general availability

InfluxDB Clustered is now generally available and gives you the power of InfluxDB v3 in your self-managed stack.

Talk to us about InfluxDB Clustered