Spark AND | OR | NOT Operators

Both PySpark & Spark AND, OR and NOT operators are part of logical operations that supports determining the conditional-based logic relation among the operands.

In Spark/PySpark SQL expression, you need to use the following operators for AND & OR.

AND - Evaluates to TRUE if all the conditions separated by && operator is TRUE.
OR - Evaluates to TRUE if any of the conditions separated by || is TRUE.

1. Logical Operations

Both PySpark & Spark supports standard logical operators such as AND, OR and NOT. These operators take Boolean expressions as arguments and return a Boolean value.

Below we can take a look at the behavior of the Spark AND & OR operator based on the Boolean expression.

| LEFT OPERAND | RIGHT OPERAND | AND | OR | | --- | --- | --- | --- | | TRUE | TRUE | TRUE | TRUE | | TRUE | FALSE | FALSE | TRUE | | FALSE | TRUE | FALSE | TRUE | | FALSE | FALSE | FALSE | FALSE |

2. Null handling in Logical AND & OR Operations

The below table illustrates the behavior of Spark logical AND & OR operators when a NULL value is encountered.

| LEFT Operand | RIGHT Operand | OR | AND | | --- | --- | --- | --- | | TRUE | NULL | TRUE | NULL | | NULL | TRUE | TRUE | NULL | | FALSE | NULL | NULL | FALSE | | NULL | FALSE | NULL | FALSE | | NULL | NULL | NULL | NULL |

In the case of OR operations when a null value is one of the boolean expressions then

If any one of the expressions is TRUE and the Other is NULL then the result is TRUE
If any one of the expressions is FALSE and the Other is NULL then the result is NULL

In the case of AND operations when a null value is one of the boolean expressions then

If any one of the expressions is TRUE and the Other is NULL then the result is NULL
If any one of the expressions is FALSE and the Other is NULL then the result is FALSE

When both the expressions of an AND, and OR are NULL the final result is obviously NULL.

3. Spark Using And & OR Operator

Usually, AND (&&) operator is useful when you wanted to filter the Spark DataFrame by multiple conditions.

Similarly, let's use the OR (||) operator to filter the Spark DataFrame.


// Spark AND Operator 
df.filter(df("state") === "OH" && df("gender") === "M")
    .show(false)

// Spark OR Operator
df.filter(df("state") === "OH" || df("gender") === "M")
    .show(false)

4. PySpark Using OR Operator

PySpark Logical operations use the bitwise operators:

& for and
| for or
~ for not


// PySpark AND Operator 
df.filter(df("state") === "OH" & df("gender") === "M")
    .show(false)

// PySpark OR Operator
df.filter(df("state") === "OH" | df("gender") === "M")
    .show(false)

3. Conclusion

Spark & PySpark support standard logical operators such as AND, OR and NOT. These operators take Boolean expressions as arguments and return a Boolean value.