Tableau Prep as of 2019.4 does not support joining Null values, i.e. where Table A.Column A and Table B.Column A both have Null values and we want to join those two columns (potentially with other join clauses).
At this time to join on Null values in Prep (and keep Nulls in the output) we have to:
1) Recognize that Null values aren't joining. Fortunately Prep's Join step highlights this!
2) Recognize that Tableau Prep will not join null values since the interface does not tell us that, nor does the Tableau Prep documentation at Aggregate, Join, or Union Data - Tableau . There is a KB article that shows up if we enter the right search terms: https://kb.tableau.com/articles/issue/rows-with-null-in-join-key-missing-from-join that is a well-written article explaining what's happening, why, and how to work around the issue.
3) Change the Nulls to non-Null values:
a) We can do this in the Join step however if there are multiple join clauses then we can only change one tuple (set of join keys) at a time and that doesn't scale. See https://community.tableau.com/ideas/10439 for more info.
b) Or we have to go to upstream steps for each of the left and right sides of the join and either edit the Null value with a Group and Replace to be something like "[impute Null for join]" or a dummy number/date, or use a calculated field with IFNULL() per the KB article.
4) Then the join will work, however downstream of the join to display Nulls in the output then we need to rewrite the imputed values from step 3 to be Null with additional Group and Replaces or calculated fields.
I've attached a v2019.4 packaged workflow demonstrating this.
The Cause section of the KB article (linked to above) states, "In a relational database, a null is not a value, a null is the absence of a value. Because of this, two nulls are not treated as logically equivalent, and null join key values are ignored."
This makes complete sense from a DBA perspective, however it's not necessarily accurate nor useful from a user perspective where:
a) We're often joining manually created Excel or other data that stretches the meaning of "relational".
b) We're often joining data that was *not* designed by a DBA in the first place so Null values are given actual meaning, for example in survey data Null often effectively means "did not answer".
c) Tableau Prep is not a standard query tool for relational databases, instead it's designed to be an easy to use data preparation tool which means it has to work with messy, non-conforming data as-is and the current functionality that does not support joining on Null values does not make that easy.
Also, by way of comparison, Tableau Desktop supports joining on Null values for text & Excel sources as do other data preparation tools, so the lack of this functionality in Prep is confusing to users making the transition. (In fact, it was a user reaching out for help that was the inspiration for this idea).
Personally I'd like to have an option in the configuration of the Join step to "Join Null values to Null values" similar to what we have in Tableau Desktop, and ideally this would work for any input source of the data, not just Excel or text files.