<p>Spark提供了一个特殊的<code>NULL</code>安全相等运算符:</p>
<pre><code>numbersDf
.join(lettersDf, numbersDf("numbers") <=> lettersDf("numbers"))
.drop(lettersDf("numbers"))
</code></pre>
<pre class="lang-none prettyprint-override"><code>+-------+-------+
|numbers|letters|
+-------+-------+
| 123| abc|
| 456| def|
| null| zzz|
| | hhh|
+-------+-------+
</code></pre>
<p>小心不要与Spark 1.5或更早版本一起使用。在Spark 1.6之前,它需要笛卡尔积(<a href="https://issues.apache.org/jira/browse/SPARK-11111" rel="noreferrer">SPARK-11111</a>-<em>快速零安全连接</em>)。</p>
<p>在<strong>Spark 2.3.0或更高版本中,您可以在<strong>PySpark中使用<code>Column.eqNullSafe</code>:</p>
<pre><code>numbers_df = sc.parallelize([
("123", ), ("456", ), (None, ), ("", )
]).toDF(["numbers"])
letters_df = sc.parallelize([
("123", "abc"), ("456", "def"), (None, "zzz"), ("", "hhh")
]).toDF(["numbers", "letters"])
numbers_df.join(letters_df, numbers_df.numbers.eqNullSafe(letters_df.numbers))
</code></pre>
<pre class="lang-none prettyprint-override"><code>+-------+-------+-------+
|numbers|numbers|letters|
+-------+-------+-------+
| 456| 456| def|
| null| null| zzz|
| | | hhh|
| 123| 123| abc|
+-------+-------+-------+
</code></pre>
<p>以及<code>%<=>%</code>在<strong>SparkR中:</p>
<pre><code>numbers_df <- createDataFrame(data.frame(numbers = c("123", "456", NA, "")))
letters_df <- createDataFrame(data.frame(
numbers = c("123", "456", NA, ""),
letters = c("abc", "def", "zzz", "hhh")
))
head(join(numbers_df, letters_df, numbers_df$numbers %<=>% letters_df$numbers))
</code></pre>
<pre class="lang-none prettyprint-override"><code> numbers numbers letters
1 456 456 def
2 <NA> <NA> zzz
3 hhh
4 123 123 abc
</code></pre>
<p>使用<strong>SQL</strong>(<strong>Spark 2.2.0+</strong>)可以使用<code>IS NOT DISTINCT FROM</code>:</p>
<pre class="lang-sql prettyprint-override"><code>SELECT * FROM numbers JOIN letters
ON numbers.numbers IS NOT DISTINCT FROM letters.numbers
</code></pre>
<p>这也可以与<code>DataFrame</code>API一起使用:</p>
<pre class="lang-scala prettyprint-override"><code>numbersDf.alias("numbers")
.join(lettersDf.alias("letters"))
.where("numbers.numbers IS NOT DISTINCT FROM letters.numbers")
</code></pre>