How to avoid the query not yielding results when the missing value is null in multi-criteria matching?

[#1]

When join tables with more than one matching condition, for example, matching according to USL, LSL and Nominal in two tables, the lookup will be empty when there is only upper limit or only lower limit, i.e., when the lower limit is a missing value or the upper limit is a missing value, how to solve this solution? Are all the missing values replaced with 9999？

Thanks！

[#2]

Could you provide some example tables? If both tables have same values I think the joins should work fine

[#3]

Thanks, I know what you mean.
But my data table has more than 110,000 rows and only 1400 rows are having problems connecting, three of the four match conditions are missing values, I change the missing value to 9999 and it matches, after checking for a long time it didn't.
In addition, there are also many cases where the three matching conditions are missing, but still connected!

[#4]

What if you replace the missing value to 9999 (recode null to 9999) before join-matching?

[#5]

Join with multiple match conditions always checks ALL matches and just generates a row when there is a match for all conditions.

If there are missing values in one of the tables, a match means: missing in both tables (see examples in  @jthi reply).

Stated differently:  join with missing value doesn't mean: 

If a value is missing, ignore this match condition for the specific row.