> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/join.md). # join Use the `join` stage to combine the results of two queries into a single result set based on a specified condition. ## Syntax ```sql join conflict_strategy = both|left|right type = inner|left|right (() as ) ``` ## Parameters | Name | Type | Required | Description | | ------------------- | ----------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `conflict_strategy` | string | No | Identifies how column name conflicts are resolved if a field name exists in both the parent (left) query's result set and the joined (right) query's result set. Valid values are `both`, `left`, or `right`. The default is `right`. | | `type` | string | No | Defines the type of join, dictating which records are included in the final result set based on the join condition. Valid values are `inner`, `left`, or `right`. The default is `inner`. | | `xql query` | query block | Yes | The XQL query whose results you want to combine with the parent query. This sub-query must be enclosed in parentheses. | | `execution_name` | string | Yes | Provides an alias for the joined query's result set using the `as` clause. This alias is used to refer to fields from the joined query (for example, `alias.field_name`). | | `boolean_expr` | expression | Yes | Identifies the conditions (join keys) that must be met to place a record in the join result set. | ## Returns The `join` stage returns a unified result set containing rows from the parent query combined with rows from the sub-query, based on the specified join type and condition. ## Usage notes * The `join` stage can combine results, but it does not preserve sort order. If sorting is needed, specify the `sort` stage **after** the `join` stage. * `join` operations, especially on large datasets like `xdr_data`, can be resource-intensive. It is a Best practice to pare down datasets before joining them. * Always use the `fields` stage early in your queries, including within the sub-queries of a `join`, to select only necessary columns. This minimizes the data processed and improves performance. * The `config case_sensitive` stage can be used at the beginning of the query or when adding a `join` stage to control case sensitivity for field value evaluation. * By default, forensic datasets are not included in XQL query results unless explicitly defined. Queries for forensic data require specific enabling steps. ## Examples ### Example 1: Inner join **Goal**: Perform an inner join to return only the records where there is a match in `event_id` from both the left and right datasets. **XQL code**: ```sql config timeframe = 1d | dataset = sample_xql_raw | filter event_id in (101, 102, 103, 104) // Simulates left side | fields event_id, event_description | join type = inner ( dataset = sample_xql_raw | filter event_id in (103, 104, 105, 106) // Simulates right side | fields event_id as joined_event_id, is_successful as success_status ) as right_data right_data.joined_event_id = event_id // Join condition ``` **Explanation**: This query joins records from `sample_xql_raw` where `event_id` is 101, 102, 103, or 104, with records where `event_id` is 103, 104, 105, or 106. Only `event_id`s present in both sets will be returned. The `joined_event_id` from `right_data` is matched with `event_id` from the main query. **Output**: | event\_id | event\_description | joined\_event\_id | success\_status | | --------- | ------------------------------ | ----------------- | --------------- | | 103 | Network connection established | 103 | true | | 104 | System heartbeat | 104 | true | ### Example 2: Left join **Goal**: Perform a left join to return all records from the left dataset, and the matching records from the right. **XQL code**: ```sql config timeframe = 1d | dataset = sample_xql_raw | filter event_id in (101, 102, 103, 104) // Simulates left side | fields event_id, event_description | join type = left ( dataset = sample_xql_raw | filter event_id in (103, 104, 105, 106) // Simulates right side | fields event_id as joined_event_id, is_successful as success_status ) as right_data right_data.joined_event_id = event_id // Join condition ``` **Explanation**: This query returns all `event_id`s 101, 102, 103, 104 from the left side. For `event_id`s 101 and 102, since there are no matching records on the right side, `NULL` is populated for `joined_event_id` and `success_status`. **Output**: | event\_id | event\_description | joined\_event\_id | success\_status | | --------- | ------------------------------ | ----------------- | --------------- | | 101 | User login successful | NULL | NULL | | 102 | File access attempt | NULL | NULL | | 103 | Network connection established | 103 | true | | 104 | System heartbeat | 104 | true | ### Example 3: Right join **Goal**: Perform a right join to return all records from the right dataset, and the matching records from the left. **XQL code**: ```sql config timeframe = 1d | dataset = sample_xql_raw | filter event_id in (101, 102, 103, 104) // Simulates left side | fields event_id, event_description | join type = right ( dataset = sample_xql_raw | filter event_id in (103, 104, 105, 106) // Simulates right side | fields event_id as joined_event_id, is_successful as success_status ) as right_data right_data.joined_event_id = event_id // Join condition ``` **Explanation**: This query returns all `event_id`s 103, 104, 105, 106 from the right side. For `event_id`s 105 and 106, since there are no matching records on the left side, `NULL` is populated for `event_description`. **Output**: | event\_id | event\_description | joined\_event\_id | success\_status | | --------- | ------------------------------ | ----------------- | --------------- | | 103 | Network connection established | 103 | true | | 104 | System heartbeat | 104 | true | | NULL | NULL | 105 | true | | NULL | NULL | 106 | false | ### Example 4: Conflict strategy left **Goal**: Perform an inner join while prioritizing the column from the original (parent) query's result set when a conflict exists. **XQL code**: ```sql config timeframe = 1d | dataset = sample_xql_raw | filter event_id in (101, 102, 103, 104) // Simulates left side with original event_description | fields event_id, event_description | join conflict_strategy = left // Prioritize left side event_description type = inner ( dataset = sample_xql_raw | filter event_id in (103, 104, 105, 106) // Simulates right side data for join | fields event_id as joined_event_id, is_successful as event_description // This event_description will be discarded due to conflict_strategy = left ) as right_data right_data.joined_event_id = event_id // Join condition | fields event_id, event_description, joined_event_id // Select fields to show output ``` **Explanation**: The sub-query renames `is_successful` to `event_description` to create a conflict. Since `conflict_strategy = left` is specified, the `event_description` from the primary (left) query, which contains string values (for example, "Network connection established"), is preserved. **Output**: | event\_id | event\_description | joined\_event\_id | | --------- | ------------------------------ | ----------------- | | 103 | Network connection established | 103 | | 104 | System heartbeat | 104 | ### Example 5: Conflict strategy right **Goal**: Perform an inner join while prioritizing the column from the inner (joined) query's result set when a conflict exists. **XQL code**: ```sql config timeframe = 1d | dataset = sample_xql_raw | filter event_id in (101, 102, 103, 104) // Simulates left side with original event_description | fields event_id, event_description | join conflict_strategy = right // Prioritize right side event_description type = inner ( dataset = sample_xql_raw | filter event_id in (103, 104, 105, 106) // Simulates right side data for join | fields event_id as joined_event_id, is_successful as event_description // This event_description will be kept ) as right_data right_data.joined_event_id = event_id // Join condition | fields event_id, event_description, joined_event_id // Select fields to show output ``` **Explanation**: With `conflict_strategy = right`, the `event_description` column from the inner (right) query is kept. This results in the `event_description` column containing the boolean values from `is_successful` (for example, `true`), while the original string `event_description` from the left query is discarded for the joined records. **Output**: | event\_id | event\_description | joined\_event\_id | | --------- | ------------------ | ----------------- | | 103 | true | 103 | | 104 | true | 104 | ## Related articles * **Stages**: [`config`](/xql-command-reference-guide/readme/stages/config.md), [`dataset`](/xql-command-reference-guide/readme/stages/dataset.md), [`fields`](/xql-command-reference-guide/readme/stages/fields.md), [`filter`](/xql-command-reference-guide/readme/stages/filter.md), [`sort`](/xql-command-reference-guide/readme/stages/sort.md) * **Datasets**: [`xdr_data`](https://www.google.com/search?q=%5Bhttps://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction%5D\(https://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction\)) --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/join.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.