Feature Request - It would be nice if we could join on null without having to do extra work

Database
Junior Supporter

Feature Request - It would be nice if we could join on null without having to do extra work

There are times when I want to give a custom classification to all occurances of null in a different table.

It would be nice if we could join on null without having to do the extra work of a case statement or coalesce or casting ect...

Tags (1)
3 REPLIES
Teradata Employee

Re: Feature Request - It would be nice if we could join on null without having to do extra work

All occurrences of null in a column are identical to each other.

Please explain what the join criteria would be. How would certain null values in one table's column be related to other null values in a different table's column?

Junior Supporter

Re: Feature Request - It would be nice if we could join on null without having to do extra work

Yes and it doesn't matter that they are identical. I am trying to re-classify all null values with as little work as possible.

For example:

Table 1 - Data Table

Dog

null

Man

null

Pizza

Table 2 - Custom Classification table

Data         Custom Classification

Dog          Animal

null          Other

Man         Human

Pizza        Food

Junior Contributor

Re: Feature Request - It would be nice if we could join on null without having to do extra work

What you're asking for will break any existing code and compatibility with Standard SQL.

Is it so much work to add another condition to the join?

ON (t1.col = t2.col OR (t1.col IS NULL AND t2.col IS NULL))

This approach is preferred over COALESCE, etc. (it's the only case when OR in a join is recommended)