A table consists of a set of rows and each row contains a set of columns.
A column is associated with a data type and represents
a specific attribute of an entity (for example,
age is a column of an
person). Sometimes, the value of a column
specific to a row is not known at the time the row comes into existence.
SQL, such values are represnted as
NULL. This section details the
NULL values handling in various operators, expressions and
- Null handling in comparison operators
- Null handling in Logical operators
- Null handling in Expressions
- Null handling in WHERE, HAVING and JOIN conditions
- Null handling in GROUP BY and DISTINCT
- Null handling in ORDER BY
- Null handling in UNION, INTERSECT, EXCEPT
- Null handling in EXISTS and NOT EXISTS subquery
- Null handling in IN and NOT IN subquery
The following illustrates the schema layout and data of a table named
person. The data contains
NULL values in
age column and this table will be used in various examples in the sections below.
Apache spark supports the standard comparison operators such as ‘>’, ‘>=’, ‘=’, ‘<’ and ‘<=’.
The result of these operators is unknown or
NULL when one of the operarands or both the operands are
NULL. In order to compare the
NULL values for equality, Spark provides a null-safe
equal operator (‘<=>’), which returns
False when one of the operand is
NULL and returns ‘True
both the operands are NULL
. The following table illustrates the behaviour of comparison operators when
one or both operands are NULL`:
|Left Operand||Right Operand||>||>=||=||<||<=||<=>|
Spark supports standard logical operators such as
NOT. These operators take
as the arguments and return a
The following tables illustrate the behavior of logical opeators when one or both operands are
|Left Operand||Right Operand||OR||AND|
The comparison operators and logical operators are treated as expressions in Spark. Other than these two kinds of expressions, Spark supports other form of expressions such as function expressions, cast expressions, etc. The expressions in Spark can be broadly classified as :
- Null in-tolerent expressions
- Expressions that can process
- The result of these expressions depends on the expression itself.
Null in-tolerant expressions return
NULL when one or more arguments of
NULL and most of the expressions fall in this category.
This class of expressions are designed to handle
NULL values. The result of the
expressions depends on the expression itself. As an example, function expression
true on null input and
false on non null input where as function
returns the first non
NULL value in its list of operands. However,
NULL when all its operands are
NULL. Below is an incomplete list of expressions of this category.
Aggregate functions compute a single result by processing a set of input rows. Below are
the rules of how
NULL values are handled by aggregate functions.
NULLvalues are ignored from processing by all the aggregate functions.
- Only exception to this rule is COUNT(*) function.
- Some aggregate functions return
NULLwhen all input values are
NULLor the input data set is empty.
The list of these functions is:
HAVING operators filter rows based on the user specified condition.
JOIN operator is used to combine rows from two tables based on a join condition.
For all the three operators, a condition expression is a boolean expression and can return
True, False or Unknown (NULL). They are “satisfied” if the result of the condition is
As discussed in the previous section comparison operator,
NULL values are not equal. However, for the purpose of grouping and distinct processing, the two or more
NULL dataare grouped together into the same bucket. This behaviour is conformant with SQL
standard and with other enterprise database management systems.
Spark SQL supports null ordering specification in
ORDER BY clause. Spark processes the
ORDER BY clause by
placing all the
NULL values at first or at last depending on the null ordering specification. By default, all
NULL values are placed at first.
NULL values are compared in a null-safe manner for equality in the context of
set operations. That means when comparing rows, two
NULL values are considered
equal unlike the regular
In Spark, EXISTS and NOT EXISTS expressions are allowed inside a WHERE clause.
These are boolean expressions which return either
FALSE. In otherwords, EXISTS is a membership condition and returns
when the subquery it refers to returns one or more rows. Similary, NOT EXISTS
is a non-membership condition and returns TRUE when no rows or zero rows are
returned from the subquery.
These two expressions are not affected by presence of NULL in the result of the subquery.
NOT IN expressions are allowed inside a WHERE clause of
a query. Unlike the
IN expression can return a
UNKNOWN (NULL) value. Conceptually a
IN expression is semantically
equivalent to a set of equality condition separated by a disjunctive operator (
For example, c1 IN (1, 2, 3) is semantically equivalent to
(C1 = 1 OR c1 = 2 OR c1 = 3).
As far as handling
NULL values are concerned, the semantics can be deduced from
NULL value handling in comparison operators(
=) and logical operators(
To summarize, below are the rules for computing the result of an
- TRUE is returned when the non-NULL value in question is found in the list
- FALSE is returned when the non-NULL value is not found in the list and the list does not contain NULL values
- UNKNOWN is returned when the value is
NULL, or the non-NULL value is not found in the list and the list contains at least one