If you include multiple sources in a query, create relationships in Builder to define how to combine their records in the results, based on a shared value such as entity ID, account number, or department.
Note: In SQL Editor, use a JOIN clause to define relationships between a query's sources.
For example, you can define how to combine records in an Orders data table:
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 111222 | 001 | 01/02/2021 |
| 121212 | 010 | 02/01/2021 |
| 212121 | 100 | 01/12/2021 |
with those in a Customers dimension table:
| CustomerID | CustomerName | Region |
|---|---|---|
| 001 | AAA Dry Cleaners | North |
| 010 | Wayne Technology | East |
| 011 | Señor Taqueria | West |
Tip: To identify a column with unique values—such as CustomerID—in a dimension table, mark it as a Key column.
Since both tables include customer IDs, you can create a relationship based on their respective CustomerID columns and select how to combine—or join—their records.
JOIN Types
When you create a relationship between multiple query sources, you select its JOIN type to specify how to combine their records in the results.
Left Join
A Left Join relationship returns all records from the first source—on the left—and any also matched in the other.
Note: To avoid duplicates in a Left Join, ensure the second source column—on the right in the relationship—contains unique values.
For example, in the relationship between our Orders and Customers tables, a Left Join returns all orders, with additional details about those with customer IDs matched in the Customers table. Orders with customer IDs not matched in the Customers table include null values for the additional customer data.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
| 212121 | 100 | -------------------- | 01/12/2021 | ------- |
Right Join
A Right Join relationship returns all records from the second source—on the right—and any also matched in the other.
Note: To avoid duplicates in a Right Join, ensure the first source column—on the left in the relationship—contains unique values.
In the case of our Orders and Customers tables, a Right Join returns all customers, with additional details for those with IDs matched in the Orders table. Customers with IDs not matched in the Orders table include null values for the additional order data.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
| -------- | 011 | Señor Taqueria | ------------- | West |
Inner Join
An Inner Join relationship returns only records that appear in both sources.
In our Orders and Customers tables' relationship, an Inner Join returns only orders from customers with IDs matched in both sources.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
Full Join
A Full Join relationship returns all records from either source.
In the example of our Orders and Customers tables, a Full Join returns all orders and customers. Records with customer IDs matched in only one source include null values for missing data.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
| -------- | 011 | Señor Taqueria | ------------- | West |
| 212121 | 100 | -------------------- | 01/12/2021 | ------- |
Define a Source Relationship
To create a relationship between two sources, specify which of their columns share common data and how to join their records:
- In the query, select the Relationships tab.
- From the Sources left panel, drag the corresponding columns from each source to under Relationships.
- Select the type of join to use to combine the sources' records:
JOIN type Returns Left Join All records from the first source—on the left in the relationship—and any also matched in the second—or right—source Right Join All records from the second source—on the right in the relationship—and any also matched in the first—or left—source Inner Join Only records matched in both sources Full Join All records from either source - Click Save .
Sort a Query's Relationships
After you add a query's relationships, you can sort them in the order they should occur, such as if one depends on another's join. To adjust a relationship's position in the order, select Move up or Move down from its menu.
Remove a Relationship
To delete a relationship from a query, such as if you no longer use its sources, select Remove from its menu.