While connecting to a data extension, you can create a connection between the data extension and engagement data. This way when querying, you’re pulling in data from both datasets.
Required Editions
| Available in: Intelligence Reports Advanced |
This step is optional and is only necessary if you want to query your data extension data with your engagement data using the Query Builder. If you want to query your data extension as a standalone table without engagement data, then don’t create a join.
There are two different types of joins
- Left Join—Returns all records from the left table, and the matched records from the right table. This is the default type.
- Full Join—Returns all records when there’s a match in either the left or right table.
Use this example, which demonstrates subscriber data, to understand what happens when selecting each join type. You can perform the join on any field.
Engagement Data
| Subscriber Key | Sends |
|---|---|
| abc123 | 1 |
| def123 | 1 |
| aaa222 | 1 |
Data Extension Data
| ID | Region |
|---|---|
| abc123 | EMEA |
| def123 | AMER |
| 123456 | AMER |
| aaa222 | EMEA |
Since the values are stored in different fields in the data extension and in the engagement data, you must ensure that the fields are connected. If subscriber data is represented as the subscriber key in your engagement data, and as contact ID in the data extension data, connect the fields like this:
By default, the system performs a left join. If you run the query, you get these results based on the type of join you select.
Left Join Results
| ID | Region | Sends |
|---|---|---|
| abc123 | EMEA | 1 |
| def123 | AMER | 1 |
| aaa222 | EMEA | 1 |
Full Join Results
| ID | Region | Sends |
|---|---|---|
| abc123 | EMEA | 1 |
| def123 | AMER | 1 |
| aaa222 | EMEA | 1 |
| 123456 | AMER | NULL |