How to combine two DataFrames with no common columns in Apache Spark

In this article, I will show you how to combine two Spark DataFrames that have no common columns.

For example, if we have the two following DataFrames:

val df1 = Seq(
        ("001","002","003"),
        ("004","005","006")
    ).toDF("A","B","C")
val df2 = Seq(
        ("011","022","033"),
        ("044","055","066")
    ).toDF("D","E","F")

The output I want to get looks like this:

+----+----+----+----+----+----+
|   A|   B|   C|   D|   E|   F|
+----+----+----+----+----+----+
| 001| 002| 003|null|null|null|
| 004| 005| 006|null|null|null|
|null|null|null| 011| 022| 033|
|null|null|null| 044| 055| 066|
+----+----+----+----+----+----+

This can be easily achieved by using the full outer join with the condition set to false:

df1.join(df2, lit(false), "full")

It works because the full outer join takes all rows from both DataFrames, so we end up with all rows, and we use lit(false) as the joining condition, which ensures that there will be no matches between both DataFrames.

Older post

How to get names of columns with missing values in PySpark

How to get the names of missing properties for every row in a PySpark Dataframe

Newer post

How to decode base64 to text in AWS Athena

How to use from_base64 in AWS Athena