question regarding join and null

[dnat]

Hi

How does datastage handle null in the input key column and the reference key column.

I have millions of data in the input as well as reference and the key columns which i use to join is having nulls.

So, will the null in input join with the null in reference?. Because i am doing a left join and getting huge number of records which is not correct. I am analysing the problem, but want to know how the nulls are being handled when there is a nullable value in the join key itself.

I am suspecting that it is doing a cartesian product.

[ray.wurlod]

Officially (in any form of database query) joining on NULL is not permitted.
For the purposes of the join, you can create a new column containing a particular value (an "in-band null") if your "join key" is null, and the actual value otherwise. Use these created columns to effect the join. They can easily be created using Modify stages.

[abhijain]

I am agree with Ray. Join is not permitted on NULL.

Alternatively, you can handle NULL before performing the join operation and assign some value say '-999999' and revert it back to NULL once you are done with your join operation.

[dnat]

i checked the join conditions by creating two tables and having nulls. A null in source is joining with a null in target..so even if i change it to some other value like -1, it is going to give me the same ouput.

[priyadarshikunal]

In case keys of multiple records are null, how it can defined as key?

in databases join on nulls will not give you any results. but In datastage you will find a match. If you dont want to perform joins on null, filter it out of the reference link.