There is a known limitation about live queries that use join() functions — the details are explained at Join Operation and Optimization. You should be aware of this when you intend to create an alert with join() functions, as the alert may not trigger as expected.

Joins cannot be used in most alert queries, except Legacy alerts. However, Legacy alerts are generally not recommended. Instead, when using joins in a trigger query, create a scheduled search. The limitation means that Legacy alerts cannot be expected to work in combination with these particular query functions.

The UI warns you about this behavior before you save the alert.

Figure 206. Warning Using Live join()