TSQL INNER JOINS and null fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: PMcG (anonymous_at_discussions.microsoft.com)
Date: 05/11/04 

Date: Tue, 11 May 2004 01:36:02 -0700

I have a inner join query, the following is put togethere to illustrate my query, if anyone can explain this i would appreciate it.
Thanks in advance
Pat

/*
If i have a table Table1 with the following content (Note SecondaryIdentifier is varchar and nullable)
OGPT_PartyId OGPT_PrimaryIdentifier SecondaryIdentifier
1 id1 NULL
1 id1 id2

and i run the first statement i only get the second record, if i run the second statement i get both records, i'm not intrested in the statements thenseleves,i'm just wondering why the first statement will not return both records as the seondry identifier field contains null and and i would expect that to match.
*/

-- Statement 1
SELECT
        t1.OGPT_PartyId,
        t1.OGPT_PrimaryIdentifier,
        t1.OGPT_SecondaryIdentifier,
FROM
        dbo.[Table1] as t1
                INNER JOIN dbo.[Table1] as t2
        ON
                t1.OGPT_PartyId = t2.OGPT_PartyId AND
                t1.OGPT_PrimaryIdentifier = t2.OGPT_PrimaryIdentifier AND
                t1.OGPT_SecondaryIdentifier = t2.OGPT_SecondaryIdentifier

-- Statement 2
SELECT
        t1.OGPT_PartyId,
        t1.OGPT_PrimaryIdentifier,
        t1.OGPT_SecondaryIdentifier,
FROM
        dbo.[Table1] as t1
                INNER JOIN dbo.[Table1] as t2
        ON
                t1.OGPT_PartyId = t2.OGPT_PartyId AND
                t1.OGPT_PrimaryIdentifier = t2.OGPT_PrimaryIdentifier AND
                t1.OGPT_SecondaryIdentifier = t2.OGPT_SecondaryIdentifier
WHERE
        t1.OGPT_SecondaryIdentifier IS NOT NULL AND
        t2.OGPT_SecondaryIdentifier IS NOT NULL
UNION
SELECT
        t1.OGPT_PartyId,
        t1.OGPT_PrimaryIdentifier,
        t1.OGPT_SecondaryIdentifier,
FROM
        dbo.[Table1] as t1
                INNER JOIN dbo.[Table1] as t2
        ON
                t1.OGPT_PartyId = t2.OGPT_PartyId AND
                t1.OGPT_PrimaryIdentifier = t2.OGPT_PrimaryIdentifier
WHERE
        t1.OGPT_SecondaryIdentifier IS NULL AND
        t2.OGPT_SecondaryIdentifier IS NULL