...
You cannot create alerts on tables that contain lookup data (my.lookuplist tables) as the New Alert Definition button will not appear in the toolbar.
Restriction 4: subqueries
You cannot create alerts using queries that contain subqueries. If you try, the alert definition window will show a warning at the bottom, and clicking the create button will have no effect.
...
The only exception to this rule is the each-type alert, which allows the use of subqueries with specific limitations. For more details on these restrictions and all the features of this type of alerts, please visit this article.