What to do in SQL when you want NULL = NULL to be true?

Question

Imagine a table (named Example) with a single primary key field (KeyID) and also a foreign key field (ForeignID). This foreign key is used to link rows in our table to a foreign row/item in a separate table, establishing a many-to-one relationship. For many of our rows, however, no such relationship exists, so our foreign key field is NULL in these rows.

Now, if we are given a single KeyID (e.g. 123), what is the preferred SQL for getting a result set containing all rows that have a matching ForeignID value?

I naively started with the following SQL:

SELECT E1.*
FROM Example E1
JOIN Example E2
  ON E2.KeyID = 123
  AND E2.ForeignID = E1.ForeignID

This works just great when our matching key row has a normal value in ForeignID. However, it fails (returning nothing) if the ForeignID happens to be NULL. After doing some initial searching, I now understand why (after reading questions like this one), but I haven't found any nice solutions for how to work around this limitation.

Granted SQL Server has the ANSI_NULLS setting that I can change, but this seems like a dirty, potentially problematic hack.

Alternatively, I could always make up my own pseudo-null value (like 0) and stick it in the ForeignID column instead of NULL, but that would break the foreign key constraint I've established for this column.

So how best to achieve what I want?

Jeff Rosenberg · Accepted Answer · 2013-04-09T20:34:59.353

8

Untested, but I'd think you could do this:

SELECT E1.*
FROM Example E1
JOIN Example E2
  ON E2.KeyID = 123
  AND (E2.ForeignID = E1.ForeignID
    OR (E2.ForeignID IS NULL AND E1.ForeignID IS NULL))

Edit: Works, SQL Fiddle here

edited Apr 09 '13 at 20:34

answered Apr 09 '13 at 20:28

Jeff Rosenberg

3,522
1
18
38

2

You should probably put some brackets in there to make it more obvious what you're doing, to both OP and SQL Server – Blorgbeard Apr 09 '13 at 20:29
Duh, the answer is simple, easy, and obvious, so of course I didn't think of it myself. -_- – Jeremy Apr 09 '13 at 20:30
Note that this will result in a Cartesian join of the NULL rows from E1 with the NULL rows from E2. As long as one of the tables has just one row with NULL, you should be fine. – N West Apr 09 '13 at 20:30
1

@Blorgbeard `a OR b AND c` does not need extra brackets, it always means `a OR (b AND c)`. Readability to OP is a fair point, readability to SQL Server is not. – Apr 09 '13 at 20:32

score 7 · Answer 2 · edited Nov 13 '18 at 23:30

7

You can also do

SELECT E1.*
FROM   Example E1
       JOIN Example E2
         ON E2.KeyID = 123
            AND EXISTS (SELECT E2.ForeignID
                        INTERSECT
                        SELECT E1.ForeignID)

edited Nov 13 '18 at 23:30

Community

1
1

answered Apr 09 '13 at 20:31

Martin Smith

438,706
87
741
845

Although `INTERSECT` isn't available in some of the databases I'm using, I'm starting to realize just how useful it can be. This answer also led me to the following helpful blog: [Undocumented Query Plans: Equality Comparisons](http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx). – Jeremy Apr 15 '14 at 17:02

What to do in SQL when you want NULL = NULL to be true?

2 Answers2