CODEWITHSUNDEEP
sqloracle-database
kyooryu
kyooryu

Reputation: 1509

Oracle - Treat join on NULL as true

I have a table which I have to join on 4 columns:

SELECT columns 
FROM table t 
LEFT JOIN other_table ot ON o.col1 = ot.col1 
    AND o.col2 = ot.col2 
    AND o.col3 = ot.col3 
    AND o.col4 = ot.col4

However, if any of the columns in other_table is null I would like to evaluate the join condition as true regardless of the value of the other column from t, for example if ot.col4 IS NULL for given row then evaluate only col1, col2 and col3 conditions when joining.

Any ideas?

Upvotes: 0

Views: 95

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

SELECT columns 
FROM table t 
LEFT JOIN other_table ot ON o.col1 = nvl(ot.col1,t.col1) 
    AND o.col2 = nvl(ot.col2, t.col2)
    AND o.col3 = nvl(ot.col3, t.col3)
    AND o.col4 = nvl(ot.col4, t.col4)

Upvotes: 2

TechDo
TechDo

Reputation: 18629

Please try:

SELECT columns 
FROM table t 
LEFT JOIN other_table ot ON o.col1 = NVL(ot.col1, o.col1)
    AND o.col2 = NVL(ot.col2, o.col2)
    AND o.col3 = NVL(ot.col3, o.col3)
    AND o.col4 = NVL(ot.col4, o.col4)

Upvotes: 4

Related Questions