join semi and anti

Use the join statement with the join_type to create semi-join or anti-join results.

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.

1results = join alias1 by (field1, ... fieldK) join_type, alias2 by (field1, ... fieldK)

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.

1c = load \"cases\";
2a = load \"accounts\";
3a = join a by Name semi, c by Name;
4a = foreach a generate ID, Industry, Name, Year, Number_of_Employees;
5a = order a by (ID);

1a = join a by (id) semi, b by (id);

1a = join a by (id) anti, b by (id);

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.

1a = join a by (id) semi, b by (id);
2a = join a by (id) anti, c by (id);

Use a join statement to query for accounts with at least 1 opportunity

1account = load \"accounts\";
2opp = load \"opportunities\";
3q = join account by (id) semi, opp by (accountId);

Use a join statement to query for accounts with opportunity amount more than 10K.

1account = load \"accounts\";
2opp = load \"opportunities\";
3opp = filter opp by amount > 10000;
4q = join account by (id) semi, opp by (accountId);

Use a join statement to query for accounts with more than 10 opportunities.

1account = load \"accounts\";
2opp = load \"opportunities\";
3opp = group opp by accountId;
4q = join account by (id) semi, opp by (accountId);
5q = foreach q generate accountId, count() as count;
6q = filter q by count > 10;

Use a join statement to query for accounts with no opportunities.

1account = load \"accounts\";
2opp = load \"opportunities\";
3q = join account by (id) anti, opp by (accountId);

Use a join statement to query for accounts with opportunities, but no orders.

1account = load \"accounts\";
2opp = load \"opportunities\";
3orders = load \"orders\";
4q = join account by (id) semi, opp by (accountId);
5q = join q by (id) anti, orders by (accountId)

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:

For SAQL, this statement:

1a = load \"accounts\";
2opp = load \"opportunities\";
3q = join a by (id) anti, opp by (account_id);
4q = foreach q generate id, name;

has the same behavior as this SQL statement:

1select 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:

1[
2    { id : null },
3    { id : 2 }
4]

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:

1q1 = load "Opportunity";
2q2 = load "Opportunity";
3
4q1 =join q1 by (AccountId) semi, q3 by (AccountId);
5
6q2 = group q2 by all;
7q1 = 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';
8q2 = 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';
9q1 = limit q1 5;
10
11qU = 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
• union

j

Overview

Quick Start

Examples

SAQL Reference

SAQL Basic Elements

SAQL Operators

SAQL Statements

arimax

cogroup

fill

filter

foreach

group-by

group-by rollup

join semi and anti

limit

load

offset

order

sample

timeseries

union

SAQL Functions

Query Performance

Previous Versions