ES|QL IN Subquery: Keep only the rows that have a field matching a reference dataset (GA) #686

Value Proposition

Analysts constantly filter events against a reference set: known-bad IPs from a threat intel index, the hosts in a maintenance window, or the users returned by another query. WHERE IN and NOT IN subqueries let users express that filter directly in ES|QL, with the reference set defined as its own ES|QL query, instead of maintaining two-field lookup indices or stitching results together on the client.

Expected Outcome

WHERE IN and NOT IN subqueries shipped in Tech Preview in Elasticsearch 9.5 (#60).

For GA, we are bringing IN subqueries to production readiness and broadening where they can be used:

• Multi-column subqueries: match rows on a combination of fields, not only a single column
• More places in a query: IN subqueries work inside EVAL and as input to == and !=
• Data Federation compatibility: IN subqueries filter rows from external datasets, and partition pruning applies through the subquery so fewer files are read
• Correctness and hardening: bug fixes and broader test coverage