SQL Server Left Join strange NULL check

Question

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.Column6 = Variable2
                AND (Variable1 IS NULL OR Function4(Object4.Column9, ?) = Variable1)
                AND MultiPartIdentifier1.Function6(Variable3, Object4.Column11, ?) = Object3.Column1
                AND (Variable4 = ? AND (Object4.Column12 = Variable5 OR (Variable5 IS NULL AND(Variable6 IS NULL OR Object4.Column12 IN (SELECT Column13 FROM Schema1.Object6(Variable6, ?)))))
                    OR (Variable4 = ? AND (Variable7 = ? OR (Variable7 = ? AND (Object4.Column12 IS NULL OR (Object4.Column12 IN (SELECT Column13 FROM Schema1.Object6(Variable6, ?))))))))

            UNION ALL

            SELECT Function4(Object1.Column4, ?) AS Column4,
                Function4(CAST(Function5(Object4.Column10) OVER (PARTITION BY Function4(Object1.Column4, ?)) AS nvarchar(7)), ?) + ? AS Column8
            FROM Object2 Object1
            LEFT JOIN Object5 Object4
                ON Object4.Column9 = Function4(Object1.Column4, ?)
                AND MultiPartIdentifier1.Function6(Variable3, Object4.Column11, ?) = Object3.Column1
                AND (Variable4 = ? AND (Object4.Column12 = Variable5 OR (Variable5 IS NULL AND(Variable6 IS NULL OR Object4.Column12 IN (SELECT Column13 FROM Schema1.Object6(Variable6, ?)))))
                    OR (Variable4 = ? AND (Variable7 = ? OR (Variable7 = ? AND (Object4.Column12 IS NULL OR (Object4.Column12 IN (SELECT Column13 FROM Schema1.Object6(Variable6, ?))))))))
            WHERE Object1.Column6 = Variable2
                AND (Variable1 IS NULL OR Function4(Object1.Column4, ?) = Variable1)
                AND Object1.Column7 = ?
                AND Object4.Column9 IS NULL
    ) Object7
    WHERE (Variable1 IS NULL OR Function4(Object7.Column4, ?) = Variable1)
    ORDER BY Column4 ASC
    FOR XML PATH (?))), ?, ?, ?)) AS Column8
FROM 
(
SELECT DISTINCT MultiPartIdentifier1.Function6(Variable3, Object4.Column11, ?) AS Column1
FROM Object5 Object4
WHERE Object4.Column6 = Variable2
    AND (Variable1 IS NULL OR Function4(Object4.Column9, ?) = Variable1)
    AND Object4.Column11 BETWEEN MultiPartIdentifier1.Function6(Variable3, Variable8, ?) AND Variable9
    AND (Variable4 = ? AND (Object4.Column12 = Variable5 OR (Variable5 IS NULL AND(Variable6 IS NULL OR Object4.Column12 IN (SELECT Column13 FROM Schema1.Object6(Variable6, ?)))))
        OR (Variable4 = ? AND (Variable7 = ? OR (Variable7 = ? AND (Object4.Column12 IS NULL OR (Object4.Column12 IN (SELECT Column13 FROM Schema1.Object6(Variable6, ?))))))))
) Object3

Answer 1

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.