| title | Azure Digital Twins query language reference - JOIN clause |
|---|---|
| titleSuffix | Azure Digital Twins |
| description | Reference documentation for the Azure Digital Twins query language JOIN clause |
| author | baanders |
| ms.author | baanders |
| ms.date | 01/27/2025 |
| ms.topic | reference |
| ms.service | azure-digital-twins |
This document contains reference information on the JOIN clause for the Azure Digital Twins query language.
The JOIN clause is used in the Azure Digital Twins query language as part of the FROM clause when you want to query to traverse the Azure Digital Twins graph.
This clause is optional while querying.
Because relationships in Azure Digital Twins are part of digital twins, not independent entities, the RELATED keyword is used in JOIN queries to reference the set of relationships of a certain type from the twin collection (the type is specified using the relationship's name field from its DTDL definition). The set of relationships can be assigned a collection name within the query.
The query must then use the WHERE clause to specify which specific twin or twins are being used to support the relationship query, which is done by filtering on either the source or target twin's $dtId value.
:::code language="sql" source="~/digital-twins-docs-samples/queries/reference.sql" id="JoinSyntax":::
The following query selects all digital twins that are related to the twin with an ID of ABC through a contains relationship.
:::code language="sql" source="~/digital-twins-docs-samples/queries/reference.sql" id="JoinExample":::
Up to five JOINs are supported in a single query, which allows for the traversal of multiple levels of relationships at once.
:::code language="sql" source="~/digital-twins-docs-samples/queries/reference.sql" id="MultiJoinSyntax":::
The following query is based on Rooms that contain LightPanels, and each LightPanel contains several LightBulbs. The query gets all the LightBulbs contained in the LightPanels of rooms 1 and 2.
:::code language="sql" source="~/digital-twins-docs-samples/queries/reference.sql" id="MultiJoinExample":::
The following limits apply to queries using JOIN.
For more information, see the following sections.
Graph traversal depth is restricted to five JOIN levels per query.
The following query illustrates the maximum number of JOIN clauses that are possible in an Azure Digital Twins query. It gets all the LightBulbs in Building1.
:::code language="sql" source="~/digital-twins-docs-samples/queries/reference.sql" id="MaxJoinExample":::
OUTER JOIN semantics aren't supported, meaning if the relationship has a rank of zero, then the entire "row" is eliminated from the output result set.
Consider the following query illustrating a building traversal.
:::code language="sql" source="~/digital-twins-docs-samples/queries/reference.sql" id="NoOuterJoinExample":::
If Building1 contains no floors, then this query will return an empty result set (instead of returning one row with a value for Building and undefined for Floor).
Relationships in Azure Digital Twins can't be queried as independent entities; you also need to provide information about the source twin that the relationship comes from. This functionality is included as part of the default JOIN usage in Azure Digital Twins through the RELATED keyword.
Queries with a JOIN clause must also filter by any twin's $dtId property in the WHERE clause, to clarify which twin(s) are being used to support the relationship query.