SQL Server Left Join strange NULL check

I've just been going through some old stored procedures, doing some optimizations and stumbled across this oddity:

SELECT ISNULL(bt.BusinessTypeID, 0) AS BusinessTypeID
FROM BusinessType bt
LEFT JOIN Business b
     ON b.BusinessTypeID = ISNULL(bt.BusinessTypeID, NULL)

This is a slightly simplified version, but line 4 is the line in question. I can't envision a scenario in which this does anything? I've checked the exec plan & results with and without this NULL check and everything seems the same.

Is this just complete madness or is there some obscure reason why this might be beneficial to leave in?

UPDATE:

In response to comments have added full query (anonymized) below, in this full version this ON Object4.Column9 = Function4(Object1.Column4, ?) corresponds to this ON b.BusinessTypeID = ISNULL(bt.BusinessTypeID, NULL). Also Object4.Column9 is a nullable foreign key & Object1.Column4 is the non nullable primary key

```
SELECT Function1() AS Column1,
Function2(Function3(Function2((
SELECT Column2 AS Column3
FROM
(
SELECT ? AS Column4,
? AS Column2
WHERE (Variable1 IS NULL OR ? = Variable1)

UNION ALL

SELECT Function4(Object1.Column4, ?) AS Column4,
Function4(Object1.Column5,?) + ? AS Column2
FROM Object2 Object1
WHERE Object1.Column6 = Variable2
AND (Variable1 IS NULL OR Object1.Column4 = Variable1)
AND Object1.Column7 = ?
) Object3
ORDER BY Column4 ASC
FOR XML PATH (?))), ?, ?, ?)) AS Column8

UNION ALL

SELECT Object3.Column1,
Function2(Function3(Function2((
SELECT Function4(Column8, ?) AS Column3
FROM
(
SELECT DISTINCT Function4(Object4.Column9, ?) AS Column4,
Function4(CAST(Function5(Object4.Column10) OVER (PARTITION BY Function4(Object4.Column9, ?)) AS nvarchar(7)), ?) + ? AS Column8
FROM Object5 Object4
WHERE Object4.Co

You are right, the code makes very little sense, in many places.

-
This part in particular is completely redundant:

ISNULL(bt.BusinessTypeID, NULL)

You can use whatever in place of ISNULL(whatever, NULL), no matter if whatever is nullable or not.

-
The other part, ISNULL(bt.BusinessTypeID, 0) is also not needed but only because bt.BusinessTypeID is not nullable (it's the PK of the table) and it's on the left side of a LEFT outer join. Therefore it can never be null when it reaches that SELECT list.

The code can be safely replaced with:

SELECT bt.BusinessTypeID
FROM BusinessType bt
LEFT JOIN Business b
     ON b.BusinessTypeID = bt.BusinessTypeID

My guess is that whoever wrote that code had a generic expression pattern that worked for both nullable and not nullable expressions and parameters and didn't care much to simplify the code. For example:

ON x.eXampleID = ISNULL(y.eXampleID, -1)

WHERE x.eXampleID = ISNULL(y.eXampleID, -1) 

SELECT ISNULL(y.eXampleID, 0) AS eXampleID

might be working OK in some cases (even if they are not the most efficient) where the first parameter of ISNULL() is a nullable expression and the second is not NULL.

StackExchange Database Administrators Q#133111, answer score: 8