IN (SELECT ...) and NOT IN (SELECT ...) lowered to semi / anti joins.
IN (SELECT ...)
NOT IN (SELECT ...)