All Forums Database
teradatatester 69 posts Joined 01/10
09 Jan 2015
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:
tomnolan 594 posts Joined 01/08
09 Jan 2015

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?

teradatatester 69 posts Joined 01/10
09 Jan 2015

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
 
 
 
 

dnoeth 4628 posts Joined 11/04
09 Jan 2015

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)

Dieter

You must sign in to leave a comment.