Everyone knows 3 min read · Conditional 12 practice problems ↓

when / otherwise

CASE WHEN logic inside a column: first match wins, and a missing otherwise means null.

On this page

Show code in

Every code block on the page follows this.

You will learn

How to write CASE WHEN logic as a column
Why the order of conditions matters
What a missing otherwise produces
How to use when inside aggregations to pivot by hand

Read first

select · 4 min read
filter / where · 4 min read

Comfortable with these? Read on.

TL;DR F.when(cond, value) chains like if / elif: the first true condition wins. Rows that match nothing get the otherwise value, or null if there is none.

What it does

It builds a conditional expression, evaluated row by row. Chain more .when() calls for more branches and finish with .otherwise().

Step by step

Input

name	salary
Chitra	95000
Asha	72000
Ben	48000
Farid	39000

Output

name	band
Chitra	senior
Asha	mid
Ben	junior
Farid	junior

1
For Chitra, salary >= 90000 is true: she gets senior and the remaining branches are not checked.
2
For Asha, the first branch is false and the second, salary >= 60000, is true: mid.
3
Ben and Farid match no branch, so they get the otherwise value.

Run the example

PySparkSpark SQL

from pyspark.sql import functions as F
result = employees.select(
    "name",
    F.when(F.col("salary") >= 90000, "senior")
     .when(F.col("salary") >= 60000, "mid")
     .otherwise("junior").alias("band"),
)

SELECT name,
       CASE WHEN salary >= 90000 THEN 'senior'
            WHEN salary >= 60000 THEN 'mid'
            ELSE 'junior'
       END AS band
FROM employees

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

Order matters

Branches are tested top to bottom. Put the most specific condition first: if salary >= 60000 came first, nobody would ever be senior, because 95,000 also passes that test.

Nulls and the missing otherwise

Without otherwise, unmatched rows get null. That is often a silent bug.
A condition that evaluates to null counts as false. F.col("dept") == "HR" is null for Farid, so he falls through to the next branch.
All branch values must have a compatible type. Mixing a string and a number makes Spark cast or, in ANSI mode, fail.

Conditional aggregation

Putting when inside an aggregate counts or sums only some rows. It is the hand-written version of a pivot, and works in a single pass.

PySparkSpark SQL

from pyspark.sql import functions as F
result = employees.agg(
    F.count(F.when(F.col("salary") >= 60000, 1)).alias("high_earners"),
    F.sum(F.when(F.col("dept") == "Sales", F.col("salary")).otherwise(0)).alias("sales_payroll"),
)

SELECT COUNT(CASE WHEN salary >= 60000 THEN 1 END)               AS high_earners,
       SUM(CASE WHEN dept = 'Sales' THEN salary ELSE 0 END)        AS sales_payroll
FROM employees

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

count skips nulls, and a when without otherwise is null for non-matching rows, so count(when(cond, 1)) counts exactly the matching rows.

Common mistakes

Writing branches from general to specific

The general branch catches everything. Order from most to least specific.

Forgetting otherwise

Unmatched rows become null and later aggregates silently skip them.

Using Python if on a column

if F.col("x") > 1: raises "Cannot convert column into bool". Conditions on columns must be when.

Key takeaways

when chains are evaluated top to bottom; the first true branch wins.
No otherwise means null for unmatched rows.
Null conditions count as false.
count(when(cond, 1)) and sum(when(...)) give conditional aggregates in one pass.

Check yourself

3 questions

1. What does F.when(F.col("x") > 10, "big") return for x = 3?

Show the answer

null. With no otherwise, rows that match no branch get null.

2. Branches are when(x > 0, "pos").when(x > 100, "huge"). What is x = 500 labelled?

Show the answer

pos. The first true branch wins, and x > 0 is checked first. Reorder so the specific branch comes first.

3. Why does F.count(F.when(cond, 1)) count only matching rows?

Show the answer

Non-matching rows produce null, and count skips nulls. when without otherwise yields null for non-matching rows, and count(col) ignores nulls.

Practice it

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

Solve: Label Late Shipments →

See all 12 problems →

Go deeper

PySpark functions
pivot PySpark functions
coalesce PySpark functions
filter / where

Primary sources: functions.when · CASE clause

← Previous
orderBy Next →
dropDuplicates