--- schema_version: "1" id: 58732a0bfbb47fb8a68578f9e0f4e741cd23ba5ddf3100d454f7ca1b1821d557 title: Relationships & Joins collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/semantic-layer/guides/query-api-in-depth-relationships.md canonical_url: https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-relationships.html source_site: developer.salesforce.com content_type: documentation content_hash: f33d8b4ef581fe7d023686cb8a31da2655562f9bc56d7bd1e98b75c5b5e0ddb7 products: - Tableau Semantics Layer APIs - Data 360 Developer Documentation --- # Relationships & Joins ## Overview Relationships connect two Semantic Data Objects so that a single query can combine their fields. When a query selects, filters, groups, or sorts fields that live on more than one data object, the query engine walks the relationships defined in the model to build the joins that bring those tables together. You do not write SQL joins or `ON` clauses in the request — you select the fields you want, and the join path is derived from the relationships in the model. A relationship carries the two endpoint data objects, one or more join criteria (the field-to-field conditions that form the join), a join operator per criterion, a cardinality hint, and a join type. Most of this is defined once in the model. At query time you influence traversal with `join_path_plan` — whether to join every related table or only the minimal set needed to answer the query. The most important thing to know: joins happen because of relationships. If two selected data objects have no relationship path between them, the query fails. Getting cardinality and join criteria right in the model is what keeps results from multiplying or dropping rows. ## Metadata in the model Relationships, their join criteria, join operators, cardinality, and join type are all defined in the Semantic Data Model as `SemanticRelationship` entities. Each relationship names a left and a right data object, a list of criteria, and (optionally) a cardinality and join type; the default join type is `Auto`. Relationships that already exist between Data Cloud objects are imported into the model automatically when the model is created. For the authoring shape and the full field list, see [Semantic Relationship](https://developer.salesforce.com/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-relationship-semanticrelationship) in the Authoring API. You reference relationships only indirectly — by selecting fields from related data objects. When you supply the model inline in the request (rather than by ID), the relationship definitions appear under `semanticRelationships` on the model, which is why the query examples below show that structure inline. ## Query usage ### Join criteria and relationship operators Each relationship declares one or more `criteria`. A criterion pairs a `leftSemanticFieldApiName` with a `rightSemanticFieldApiName` and compares them with a `joinOperator`. When `joinOperator` is omitted, the criterion defaults to `Equals`. The supported operators are `Equals`, `EqualsIgnoreCase`, `GreaterThan`, `GreaterThanOrEqual`, `LessThan`, `LessThanOrEqual`, `NotEquals`, and `NotEqualsIgnoreCase`. ```json { "semantic_model": { "apiName": "C360_Semantic_Model_Extended_b0d2", "semanticRelationships": [ { "apiName": "Contact_Point_Phone_Case", "leftSemanticDefinitionApiName": "Contact_Point_Phone", "rightSemanticDefinitionApiName": "Case", "criteria": [ { "leftSemanticFieldApiName": "Party", "rightSemanticFieldApiName": "Account", "joinOperator": "EqualsIgnoreCase" } ], "cardinality": "ManyToMany" } // ... ] // ... } } ``` > **Performance:** Prefer `Equals` over `EqualsIgnoreCase` and `NotEqualsIgnoreCase` when the join keys are already stored in the same case. Case-insensitive operators wrap both sides of the join in a case-folding function, which prevents a hash join and increases join cost. > **Performance:** Range and inequality join operators (`GreaterThan`, `GreaterThanOrEqual`, `LessThan`, `LessThanOrEqual`, `NotEquals`) can multiply rows and cannot use a hash join. Prefer an equality (`Equals`) criterion where the data model allows it. ### Join field reference types Each side of a criterion is typed by `leftFieldType` / `rightFieldType`, which controls how the engine resolves that operand: - `TableField` — a physical data-object field. This is the default when the field type is omitted. - `SemanticField` — a model-level calculated dimension or measure, referenced by its API name. - `Formula` — an inline relationship formula (see [Relationship formulas](#relationship-formulas)). A single relationship can mix reference types across its criteria. The example below joins on one `TableField`-to-`TableField` criterion and one `TableField`-to-`SemanticField` criterion, where the right side names a calculated field. ```json { "semantic_model": { "apiName": "Sales", "semanticRelationships": [ { "apiName": "relationship", "leftSemanticDefinitionApiName": "SemanticAccount__dlm", "rightSemanticDefinitionApiName": "SemanticContact__dlm", "joinType": "Left", "criteria": [ { "leftSemanticFieldApiName": "semantic__Id__c", "leftFieldType": "TableField", "rightSemanticFieldApiName": "semantic__Id__c", "rightFieldType": "TableField" }, { "leftSemanticFieldApiName": "Semantic_KQ_Id__c", "leftFieldType": "TableField", "rightSemanticFieldApiName": "calcForJoinApiName", "rightFieldType": "SemanticField" } ] } ] // ... } } ``` ### Cardinality `cardinality` tells the engine how records on one side of a relationship map to records on the other, which guides join optimization and deduplication. Valid values are `ManyToMany` (the default when omitted), `ManyToOne`, `OneToMany`, and `OneToOne`. Declaring a `OneToOne` or `ManyToOne` relationship lets the engine skip deduplication for that join; an inaccurate cardinality can cause row multiplication or missing rows. A relationship can also list multiple criteria that must all hold for two records to join. ```json { "semanticModel": { "semanticRelationships": [ { "apiName": "MediaBuy_Site", "leftSemanticDefinitionApiName": "MediaBuy", "rightSemanticDefinitionApiName": "Site", "cardinality": "ManyToOne", "criteria": [ { "leftSemanticFieldApiName": "MediaBuySiteId", "rightSemanticFieldApiName": "SiteId" }, { "leftSemanticFieldApiName": "MediaBuyDataSourceObject", "rightSemanticFieldApiName": "SiteDataSourceObject" } ] } // ... ] // ... } } ``` > **Performance:** Declare `OneToOne` or `ManyToOne` cardinality when it is accurate. The engine can then skip row deduplication for that join, giving faster queries; an inaccurate cardinality can multiply or drop rows. ### Join type and auto-join `joinType` sets the join strategy for a relationship. The user-settable values are `Auto` (the default when unset), `Left`, and `Inner`. With `Auto`, the engine resolves the concrete strategy at query time based on the fields the query touches. You can also pin a relationship to `Left` or `Inner`; a pinned relationship always joins with that strategy. A query must use one style consistently: you cannot mix `Auto` relationships with pinned (`Left`/`Inner`) relationships along the join path — the query is rejected. Pinning join types also narrows what the query can do. When every relationship a query traverses is pinned to `Left` or `Inner`, these features are not supported: level-of-detail (LOD) expressions, forecasts, advanced dimension filters, table calculations, and row count. Use `Auto` relationships for queries that need them. `Auto` relationships, in turn, cannot be combined with smart totals (grand totals and subtotals). Joins *inside* a logical view are defined by the logical view itself with explicit join types, not through top-level `semanticRelationships`. Use a relationship to connect a logical view to a data object. The example below fixes a `Left` join between a data object and a logical view. ```json { "semantic_model": { "apiName": "Sales", "semanticRelationships": [ { "apiName": "relationship", "leftSemanticDefinitionApiName": "SemanticAccount2__dlm", "rightSemanticDefinitionApiName": "logicalTable", "joinType": "Left", "criteria": [ { "leftSemanticFieldApiName": "Semantic_KQ_Id__c", "rightSemanticFieldApiName": "SemanticAccount__dlm_Semantic_KQ_Id__c" } ] } ] // ... } } ``` ### Join path plan `join_path_plan`, set under `options` on the structured query, controls how many related tables the engine brings into the join. `JOIN_ALL_TABLES` joins every table reachable through relationships, even when the query only selects fields from some of them. `JOIN_MINIMAL_TABLES` joins only the tables required to answer the query. The example below requests the all-tables plan; switch the value to `JOIN_MINIMAL_TABLES` to restrict the join to the minimal set. ```json { "structured_semantic_query": { "fields": [ { "expression": { "table_field": { "name": "Account Name", "table_name": "AccountSemanticLayer__dll" } } } ], "options": { "join_path_plan": "JOIN_ALL_TABLES", "detailed_rows": true } } // ... relationship defined in the model } ``` > **Performance:** `JOIN_MINIMAL_TABLES` joins only the tables needed to answer the query, reducing scanned rows and intermediate result size. Use `JOIN_ALL_TABLES` only when you specifically need every related table in the result. > **Performance:** Set `disallow_cross_join` to `true` to reject a query that would otherwise produce an unintended cross join (Cartesian product). ### Unions A union combines tables *vertically* — appending the rows of several data objects that share a schema — unlike a relationship join, which combines tables *horizontally* by matching keys. A union is defined inside a logical view, not as a query construct or a top-level relationship. For the union example and its model shape, see [Logical Views](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-logical-views.md#union-logical-views). ### Relationship formulas When a criterion side has `leftFieldType` or `rightFieldType` set to `Formula`, the corresponding `leftSemanticFieldApiName` / `rightSemanticFieldApiName` slot carries an inline formula expression instead of a field name. This joins tables whose columns do not match exactly — for example, normalizing case or extracting a substring before comparing. The formula is a row-level expression (no aggregation) written in the TUA dialect, using `[Table].[Field]` references. The example below joins with a `LessThan` operator where the right operand is `UPPER([SemanticContact__dlm].[Semantic_KQ_Id__c])`. ```json { "semantic_model": { "apiName": "Sales", "semanticRelationships": [ { "apiName": "relationship", "leftSemanticDefinitionApiName": "SemanticAccount__dlm", "rightSemanticDefinitionApiName": "SemanticContact__dlm", "joinType": "Left", "criteria": [ { "leftSemanticFieldApiName": "Semantic_KQ_Id__c", "leftFieldType": "TableField", "rightSemanticFieldApiName": "UPPER([SemanticContact__dlm].[Semantic_KQ_Id__c])", "rightFieldType": "Formula", "joinOperator": "LessThan" } ] } ] // ... } } ``` ## Reference | Field (wire name) | Type | Required | Description | |---|---|---|---| | `join_path_plan` | JoinPathPlan (enum) | N | Join path strategy under `options`. `JOIN_ALL_TABLES` joins every related table; `JOIN_MINIMAL_TABLES` joins only the tables needed to answer the query. | | `disallow_cross_join` | bool | N | Under `options`. When `true`, disallows cross joins in query execution. | | `semanticRelationships` | SemanticRelationship[] | N | Relationships defined on the model; each carries endpoint data objects, `criteria`, `joinOperator`, `cardinality`, and `joinType`. Defined at authoring time — see Related. | | `relationship_formula` | string | N | Expression type produced when a criterion side is typed `Formula`; carries an inline row-level join expression. | For the relationship criteria fields (`leftSemanticDefinitionApiName`, `rightSemanticDefinitionApiName`, `criteria`, `joinOperator`, `leftFieldType`, `rightFieldType`, `cardinality`, `joinType`), see the authoring entity in Related. For the full request schema, see [Request Reference](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-fields.md). ## Limitations - **A join path must exist between all queried tables.** If the selected data objects are not connected through relationships, the query fails. - **Only one relationship is allowed between any pair of tables.** Multiple relationships between the same two tables are rejected. - **Self-joins must be defined explicitly.** When a table name and its alias resolve to the same table, the relationship connecting them must be declared. - **Join criteria operands must have matching data types.** The left and right expressions of a criterion must be the same data type. - **Textual operators require textual operands.** Case-insensitive and contains-style comparisons apply only when both operands are text. - **Join criteria operands cannot be aggregated.** A criterion cannot reference an aggregate expression, and inline relationship formulas must be row-level (no aggregations, LOD expressions, or table calculations) and cannot reference other on-the-fly calculated fields. - **The relationship graph must be acyclic.** Cycles in the set of relationships are rejected. ## Related - [Semantic Relationship](https://developer.salesforce.com/docs/data/semantic-layer/guide/connect-api-in-depth-entities.md#semantic-relationship-semanticrelationship) — Authoring API for defining relationships, join criteria, operators, cardinality, and join type. - [Dimensions, Measurements & Semantic Data Objects](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-fields.md) — The data objects that relationships connect and how to select their fields. - [Calculated Fields](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-calculated-fields.md) — Calculated dimensions and measures referenced by `SemanticField`-typed join criteria. - [Filtering](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-filtering.md) — How filters interact with joins across related tables. - [Logical Views](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-logical-views.md) — Standard, union, and custom SQL logical views, including the union example. - [Request Reference](https://developer.salesforce.com/docs/data/semantic-layer/guide/query-api-in-depth-fields.md) — Complete field schema for query options and the model.