Everyone knows 4 min read · Selection 22 practice problems ↓

filter / where

Keep the rows that match a condition, and the null and operator traps that silently drop data.

On this page

Show code in

Every code block on the page follows this.

You will learn

How to keep rows with one or several conditions
Why & and | need parentheses in PySpark
How nulls make rows disappear from both sides of a condition
How filters are pushed down to the file scan

Read first

select · 4 min read

Comfortable with these? Read on.

TL;DR filter (alias where) keeps the rows where a condition is true. Rows where it is false or null are dropped. Combine conditions with &, | and ~, each side in parentheses.

What it does

filter and where are the same method. They take a boolean column expression, or a SQL string, and return only the rows where it evaluates to true.

PySparkSpark SQL

employees.filter(F.col("salary") > 60000)
employees.where("salary > 60000")   # same thing, SQL string

SELECT * FROM employees WHERE salary > 60000

Step by step

Keep engineers, or anyone earning more than 50,000:

Input

name	dept	salary
Asha	Engineering	72000
Ben	Sales	48000
Dev	HR	51000
Farid	null	39000

Output

name	dept	salary
Asha	Engineering	72000
Dev	HR	51000

Farid has a null department. dept = 'Engineering' is null for him, not false, and he earns too little, so he is dropped.

Run the example

PySparkSpark SQL

from pyspark.sql import functions as F
result = employees.filter(
    (F.col("dept") == "Engineering") | (F.col("salary") > 50000)
)

SELECT *
FROM employees
WHERE dept = 'Engineering' OR salary > 50000

Switch to PySpark to edit and run this example in your browser.

Combining conditions

Meaning	PySpark	SQL
and	`(a) & (b)`	`a AND b`
or	`(a) \| (b)`	`a OR b`
not	`~(a)`	`NOT a`
in a list	`F.col("dept").isin("HR", "Sales")`	`dept IN ('HR', 'Sales')`
range, inclusive	`F.col("salary").between(40000, 60000)`	`salary BETWEEN 40000 AND 60000`
is null	`F.col("dept").isNull()`	`dept IS NULL`

Watch out: Python's and, or and not do not work on columns; you get "Cannot convert column into bool". And because & binds tighter than > in Python, F.col("a") > 1 & F.col("b") < 2 is parsed wrongly. Always wrap each comparison in parentheses.

Nulls: the silent row killer

SQL uses three-valued logic: a comparison with null is neither true nor false, it is null. filter keeps only true, so rows with a null in the tested column vanish from both a filter and its opposite.

PySparkSpark SQL · Two filters that do not add up to the whole table

employees.filter(F.col("dept") == "Sales").count()   # 2
employees.filter(F.col("dept") != "Sales").count()   # 3, not 4: Farid is in neither

SELECT COUNT(*) FROM employees WHERE dept = 'Sales';   -- 2
SELECT COUNT(*) FROM employees WHERE dept <> 'Sales';  -- 3

To include nulls on purpose, say so: (F.col("dept") != "Sales") | F.col("dept").isNull(), or use the null-safe comparison ~F.col("dept").eqNullSafe("Sales").

Under the hood: predicate pushdown

Catalyst moves filters as early as possible, even below joins and into the file scan. Parquet stores min and max values per row group, so a filter like salary > 90000 lets the reader skip whole row groups whose maximum is lower. In explain() you will see them as PushedFilters.

Part of explain() output

FileScan parquet [name#1,dept#2,salary#3]
  PushedFilters: [IsNotNull(salary), GreaterThan(salary,90000)]

A filter on a partition column is even better: Spark does not open the files of other partitions at all. That is partition pruning.

Why this matters: pushdown only works on plain column comparisons. Wrapping the column in a function, like F.year(F.col("ts")) == 2025, usually prevents it. Compare against a range on the raw column instead: F.col("ts") >= "2025-01-01".

Common mistakes

Using and / or instead of & / |

Python tries to turn a Column into a single bool and raises a ValueError. Use the bitwise operators.

Leaving out parentheses

F.col("a") == 1 | F.col("b") == 2 is evaluated as F.col("a") == (1 | F.col("b")) == 2. Wrap every comparison.

Comparing with None

F.col("dept") == None is always null, so the filter returns nothing. Use isNull().

Key takeaways

filter and where are the same; both keep rows where the condition is true.
Use &, |, ~ with parentheses around each comparison.
Null comparisons are null, so those rows drop out of both a filter and its opposite.
Simple column comparisons are pushed into the scan and can skip whole files and row groups.

Check yourself

3 questions

1. A table has 10 rows; 3 have a null country. filter(F.col("country") != "IN") can return at most how many rows?

Show the answer

7. The 3 null rows evaluate to null, not true, so they are never kept. At most the 7 non-null rows can pass.

2. Which filter is written correctly?

Show the answer

(F.col("a") > 1) & (F.col("b") < 5). Columns need &, and each comparison needs its own parentheses because & binds tighter than comparison operators in Python.

3. Which filter is most likely to stop Parquet row-group skipping?

Show the answer

F.year("ts") == 2025. Wrapping the column in a function hides it from the min/max statistics. A range on the raw column can be pushed down.

Practice it

Interview problems that use filter / where: write the PySpark, run it, and get graded on hidden tests.

Solve: Paid Orders in March →

See all 22 problems →

Go deeper

PySpark functions
eqNullSafe (<=>)PySpark functions
when / otherwise Lakehouse
Partitioning done right

Primary sources: DataFrame.filter · WHERE clause

← Previous
select Next →
withColumn