如何在Spark SQL中使用SPLIT函数为IN参数传入逗号分隔值

Got it, let's fix this issue you're facing. The problem with your original query is that SPLIT('red,yellow', ',') returns a single array object, but the IN clause expects a list of discrete values—not an array. That's why your initial attempt didn't work, and explode/concat_ws alone weren't solving it without the right structure.

Here are two reliable ways to make this work in Spark SQL:

Method 1: Use array_contains (Simplest Approach) #

Instead of trying to force the array into an IN clause, use array_contains to check if the color column exists inside the split array. This is the most straightforward solution:

SELECT * 
FROM TEMP tmp 
WHERE array_contains(split('red,yellow', ','), tmp.color)

This works because split('red,yellow', ',') creates an array ["red", "yellow"], and array_contains checks if each row's color value is present in that array—exactly the behavior you want from an IN clause.

Method 2: Use explode with a Subquery (If You Prefer IN) #

If you specifically want to use the IN syntax, you can first explode the split array into individual rows, then reference those rows in a subquery inside IN:

SELECT * 
FROM TEMP tmp 
WHERE tmp.color IN (
    SELECT explode(split('red,yellow', ',')) AS color
)

Alternatively, you can use a CTE to make it more readable:

WITH color_values AS (
    SELECT explode(split('red,yellow', ',')) AS color
)
SELECT tmp.* 
FROM TEMP tmp 
JOIN color_values cv ON tmp.color = cv.color

Both approaches break the comma-separated string into separate rows of color values, which the IN clause (or join) can properly match against your TEMP table's color column.

Why Your Original Query Failed #

When you wrote IN(SPLIT('red,yellow', ',')), Spark SQL treats the entire array as a single value to compare against tmp.color. Since your color column stores strings like "red" and "yellow" (not arrays), there's no match—hence no results. The explode function needs to be used in a subquery to generate individual values that IN can process.

内容的提问来源于stack exchange，提问作者CloudWarrior