# join semi and anti Use the `join` statement with the `join_type` to create semi-join or anti-join results. ## Usage A semi-join returns the rows from one data stream if one or more matching rows are found in the second data stream. Each matched row is returned one time. The row data types must match for the specified data streams. An anti-join returns the rows in the first data stream that don’t match any rows in the second data stream. ## Syntax ```nolang results = join alias1 by (field1, ... fieldK) join_type, alias2 by (field1, ... fieldK) ``` | `Name` | `Description` | | ----------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `alias1` | Required. The data stream to report semi-join or anti-join results for. | | `alias2` | Required. The data stream to look for matches or no matches in. | | `field1` | Required. The field name as it appears in the data stream. The field data type must be the same in `alias1` and `alias2` to match. Multi-value fields aren’t allowed. At least 1 field is requires, with a maximum of 5 fields allowed. Duplicate field names aren’t allowed in either data stream. | | `join_type` | Required. The type of join to run. Valid values are `semi` and `anti`. | The result stream contains the matched or unmatched rows from the `alias1` data stream only. For a semi-join, a row from `alias1` is only present if it satisfies the join criteria. The syntax supports *equijoin* (equality) criteria only. There isn't a guarantee that the rows in the result stream are in the same order as in `alias1`. The parenthesis used to specify the fields are optional if there’s only 1 field. The input data stream aliases must be unique. These streams can't be unprojected `group` or `cogroup` results, either directly or indirectly. The `group` or `cogroup` statement is made after the `join` statement. Performance Considerations - The join performance is directly proportional to the amount of data returned by the second dataset. We recommend running any filters on the second dataset before running the join. - Run the join before running any projections on the query results. For example, if you have a `foreach` statement in your query, like `q = foreach q generate count(q1) as 'A';`, run it after the `join` statement. :::note The `join` `alias` field must be a dimension or a date. If you use a measure field as an `alias`, the query returns an error stating `Error in join: non-dimension field: {field1} is not allowed in pre-projection alias: {alias1} at join keys list position: 1`. This restriction is only for pre-projection `alias`. All data types are allowed in the post-projection `alias`. For example, to use a measure in a `join`, project the measure field first. This query joins `Number_of_Employees`, a measure, by projecting it before running the `join`. ```nolang c = load \"cases\"; a = load \"accounts\"; a = join a by Name semi, c by Name; a = foreach a generate ID, Industry, Name, Year, Number_of_Employees; a = order a by (ID); ``` ::: ## Semi-Join Syntax ```nolang a = join a by (id) semi, b by (id); ``` ## Anti-Join Syntax ```nolang a = join a by (id) anti, b by (id); ``` ## Multiway Semi-Join Syntax `join` statements can be combined to form a multiway semi-join. A maximum of 3 `join` statements are allowed in a query. These statements combine into a conjunctive predicate. ```nolang a = join a by (id) semi, b by (id); a = join a by (id) anti, c by (id); ``` ## join Use Cases Use a `join` statement to query for accounts with at least 1 opportunity ```nolang account = load \"accounts\"; opp = load \"opportunities\"; q = join account by (id) semi, opp by (accountId); ``` Use a `join` statement to query for accounts with opportunity amount more than 10K. ```nolang account = load \"accounts\"; opp = load \"opportunities\"; opp = filter opp by amount > 10000; q = join account by (id) semi, opp by (accountId); ``` Use a `join` statement to query for accounts with more than 10 opportunities. ```nolang account = load \"accounts\"; opp = load \"opportunities\"; opp = group opp by accountId; q = join account by (id) semi, opp by (accountId); q = foreach q generate accountId, count() as count; q = filter q by count > 10; ``` Use a `join` statement to query for accounts with no opportunities. ```nolang account = load \"accounts\"; opp = load \"opportunities\"; q = join account by (id) anti, opp by (accountId); ``` Use a `join` statement to query for accounts with opportunities, but no orders. ```nolang account = load \"accounts\"; opp = load \"opportunities\"; orders = load \"orders\"; q = join account by (id) semi, opp by (accountId); q = join q by (id) anti, orders by (accountId) ``` ## Null Handling Running the `join` query with null fields is a special case. For the SAQL anti-join statement, null isn't equal to null, which differs from the `cogroup` statement. The behavior of the statement is the same as `NOT EXISTS` in SQL. In this example, imagine you’re joining the accounts and the opportunities data streams, which contain these rows: | `accounts` | | `opportunities` | | | ---------- | ---- | --------------- | ---- | | `id` |   | `account_id` |   | |   | 1 |   | 1 | |   | 2 |   | NULL | |   | NULL |   |   | For SAQL, this statement: ```nolang a = load \"accounts\"; opp = load \"opportunities\"; q = join a by (id) anti, opp by (account_id); q = foreach q generate id, name; ``` has the same behavior as this SQL statement: ```nolang select id, name from accounts a where not EXISTS (select 1 from opportunities opp where opp.account_id = a.id); ``` The SAQL anti-join query returns two rows: ```nolang [ { id : null }, { id : 2 } ] ``` ## Considerations Using a `union` statement and a `join` statement in the same query has strict enforcements. When a `semi` or `anti` `join` statement is present, the `union` statement returns an error if there are: - mismatched number of columns - mismatched data types The errors appear as "Different number of fields found across union streams" or "Different types found for field 1: '*fieldName*' in different union inputs." There's also strict checking on the name of the columns. Using this SAQL example with a `semi` `join` statement and a `union` statement: ```soql q1 = load "Opportunity"; q2 = load "Opportunity"; q1 =join q1 by (AccountId) semi, q3 by (AccountId); q2 = group q2 by all; q1 = foreach q1 generate q1.'AccountId' as 'AccountId',q1.StageName as 'StageName', q1.'Description' as 'Description', q1.'Id' as 'Id',q1.'Amount' as 'Amount', q1.'Probability' as 'Probability'; q2 = foreach q2 generate null as 'AccountId', null as 'StageName',null as 'Description',null as 'Id', sum(unique('Amount')) over([..] partition by all) as 'Amount_total',sum(unique('Probability')) over([..] partition by all) as 'Probability'; q1 = limit q1 5; qU = union q1, q2; ``` When the join is present, strict name checking only allows for one ‘Amount’ column name, which comes from the first stream, `q1`. The ‘Amount_total’ summary column name from the second stream, `q2`, isn’t honored and only the normal 'Amount' column name is in the union results. If the `join` statement is removed, both the normal ‘Amount’ and the summary ‘Amount_total’ column names are in the union results. **See Also** - [cogroup](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-cogroup.md) - [union](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-union.md)