Array Intersection in Spark SQL

Since Spark 2.4 array_intersect function can be used directly in SQL

spark.sql(
  "SELECT array_intersect(array(1, 42), array(42, 3)) AS intersection"
).show

+------------+
|intersection|
+------------+
|        [42]|
+------------+

and Dataset API:

import org.apache.spark.sql.functions.array_intersect

Seq((Seq(1, 42), Seq(42, 3)))
  .toDF("a", "b")
  .select(array_intersect($"a", $"b") as "intersection")
  .show

+------------+
|intersection|
+------------+
|        [42]|
+------------+

Equivalent functions are also present in the guest languages:

• pyspark.sql.functions.array_intersect in PySpark.
• SparkR::array_intersect in SparkR.

You'll need an udf:

import org.apache.spark.sql.functions.udf

spark.udf.register("array_intersect", 
  (xs: Seq[String], ys: Seq[String]) => xs.intersect(ys))

and then check if intersection is empty:

scala> spark.sql("SELECT size(array_intersect(array('1', '2'), array('3', '4'))) = 0").show
+-----------------------------------------+
|(size(UDF(array(1, 2), array(3, 4))) = 0)|
+-----------------------------------------+
|                                     true|
+-----------------------------------------+


scala> spark.sql("SELECT size(array_intersect(array('1', '2'), array('1', '4'))) = 0").show
+-----------------------------------------+
|(size(UDF(array(1, 2), array(1, 4))) = 0)|
+-----------------------------------------+
|                                    false|
+-----------------------------------------+