PySpark isin()
Performance and NOT IN (2026)
isin() with more than 10,000 values degrades to a BroadcastNestedLoopJoin. For small lists it is fast and simple. For large reference sets, switch to a semi join. Negation with ~ silently drops NULL rows.
Basic isin() Filter
from pyspark.sql import functions as F
# isin() is the PySpark equivalent of SQL IN.
# Interviewers expect you to know the performance boundary.
target_depts = ["Engineering", "Product", "Design"]
result = df.filter(F.col("department").isin(target_depts))
# Under the hood, Spark inlines these values into the plan.
# For short lists (< 1,000 values), this is fast and simple.isin() checks if a column value matches any item in a provided list. For small lists, Spark inlines the values directly into the execution plan. This is efficient for filtering on known categories, statuses, or codes.
Negation with ~ (NOT IN) and the NULL Trap
# Exclude specific statuses
result = df.filter(~F.col("status").isin(["cancelled", "refunded"]))
# WARNING: if status contains NULL values, ~isin() returns NULL
# for those rows (not True). They get filtered OUT.
# This is the same three-valued logic trap as SQL NOT IN.
# Safe version: handle NULLs explicitly
result = df.filter(
~F.col("status").isin(["cancelled", "refunded"]) |
F.col("status").isNull()
)The ~ operator negates isin(). But NULLs break the logic: ~NULL evaluates to NULL, not True. Rows with NULL in the column silently disappear from the result. A strong answer mentions this trap and handles NULLs explicitly.
Performance Limit: isin() with Large Lists
# isin() with more than 10,000 values degrades performance.
# Spark rewrites it as a BroadcastNestedLoopJoin internally,
# which is O(n*m) instead of O(n).
# Bad: 50,000 collected IDs in an isin() call
valid_ids = ref_df.select("id").rdd.flatMap(lambda x: x).collect()
result = df.filter(F.col("id").isin(valid_ids)) # slow + driver memory
# Good: use a left semi join instead. Stays distributed.
result = df.join(ref_df.select("id"), on="id", how="left_semi")
# The semi join uses a hash table on the smaller side.
# BroadcastHashJoin is O(n). BroadcastNestedLoopJoin is O(n*m).isin() with more than 10,000 values degrades to a BroadcastNestedLoopJoin internally, which is O(n*m). It also requires collecting the list to the driver, consuming driver memory. A left semi join stays distributed and uses a hash lookup at O(n). Interviewers focus on whether you know this boundary and can switch approaches.
Combining isin() with Other Conditions
# Chain with & (AND) and | (OR). Parentheses are required
# because Python operator precedence puts & above |.
result = df.filter(
F.col("department").isin(["Engineering", "Product"]) &
(F.col("salary") > 150000) &
~F.col("status").isin(["terminated"])
)
# For readability on complex filters, build conditions separately
is_target_dept = F.col("department").isin(["Engineering", "Product"])
is_senior = F.col("salary") > 150000
is_active = ~F.col("status").isin(["terminated"])
result = df.filter(is_target_dept & is_senior & is_active)Always wrap individual conditions in parentheses when using & or |. Python operator precedence can produce unexpected results without them. For complex filters, assign conditions to variables for readability.
Know PySpark isin the way the interviewer who asks it knows it.
isin looks harmless until the collection is large enough to belong in a broadcast join instead. That judgement call is the sort tested by the PySpark interview questions, and the PySpark practice problems shows which loops weight Spark at all.
Filter predicates are a common warm-up, and the trap is almost always NULL semantics rather than syntax. Interviewers who open with isin() tend to move quickly to the wider set of coding rounds, which is what the practice problem library is built to rehearse.
The Price Bander
> A catalog service segments products into price brackets before it renders them. Given `prices`, a dict mapping product names to prices, return a new dict mapping each name to its bracket: 'low' for anything under 10, 'mid' for anything under 50, and 'high' otherwise.
Sample input & expected output(1 example)
PySpark isin() FAQ
When does isin() become too slow in PySpark?+
How do I negate isin() safely with NULLs?+
What is the difference between isin() and a semi join?+
Practice PySpark isin and Filtering Before Your Interview
- 01
Reading a solution is not the same as writing one
Every engineer who has frozen on a query they had read a dozen times knows the gap. The only preparation that closes it is producing the answer yourself, under time, before the interview does it for you
- 02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
- 03
5 problem shapes cover 80% of data engineer loops
Parsing and reshaping, sessionization, dedup with tie-breaks, streaming aggregation, top-N-per-group. Writing them by hand turns the unfamiliar into pattern recognition
Related PySpark Filter Guides
Semi joins as the alternative to large isin() lists
LEFT ANTI JOIN as the safe alternative to NOT IN
The questions data engineers face in real interviews
1 data engineering challenge a week on dirty production-shaped data, scored blind on a hidden dataset. Submit before the freeze; results at the reveal.