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:
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.
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.
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.
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.
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!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.