SQL like NOT IN clause for PySpark data frames

Question

In SQL, we can for example, do select * from table where col1 not in ('A','B');

I was wondering if there is a PySpark equivalent for this. I was able to find the isin function for SQL like IN clause, but nothing for NOT IN.

Answer 1

I just had the same issue and found solution. If you want to negate any condition (in pySpark represented as Column class) there is negation operator ~, for example:

df.where(~df.flag.isin(1, 2, 3)) # records with flag NOT IN (1, 2, 3)