CODEWITHSUNDEEP
sqloracle-databasejoinnull
Theromit
Theromit

Reputation: 1

SQL join with nullable columns

I am trying to figure out what's the best approach, performance-wise, to joining two tables where one of the join columns may be null.

table1:
L_ID
U_ID
L_NAME
U_NAME

table2:
L_ID
U_ID
L_TAGS

This is on Oracle 11+, and in both the tables above, U_ID could be null. If it is null in one, it would be null in the other, fwiw.

I am currently using:

table1.L_ID = table2.L_ID
and nvl(table1.U_ID, 0) = nvl(table2.U_ID, 0)

Something tells me there may be a better way to make this work. Ideas?

Upvotes: 0

Views: 375

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Yes. Make it sargable so that index can be used if available.

where table1.L_ID = table2.L_ID
    and (
        table1.U_ID = table2.U_ID
        or (table1.U_ID is null and table2.U_ID is null )
        )

Upvotes: 1

Related Questions