How do you check if a record exists in SQL server 2012 when NULL values are possible?

Question

How would you handle a SELECT statement in SQL Server 2012 when there is a possibility that a column value might be NULL?

I am attempting to conditionally insert a record via JDBC if the record does not exist, and return the ID associated with the record if it does exist. For simplicity, I have opted to use a SELECT query, followed by an INSERT query for this.

My SELECT query looks like this, except (a, b, c, d, e) correspond to meaningful names (use your imagination).

SELECT id 
FROM dim_repository 
WHERE a = ? 
  AND b = ? 
  AND c = ? 
  AND d = ? 
  AND e = ?

However, if (a) is NULL then the query will return an empty resultset since (a) = NULL isn't a valid query (i.e. since a different verb is used for NULLs and scalar values).

Would the only way to fix the query be to add a conditional selection as follows?

SELECT id 
FROM dim_repository 
WHERE (a = ? OR a IS ?) 
  AND (b = ? OR b IS ?) 
  AND (c = ? OR c IS ?) 
  AND (d = ? OR d IS ?)
  AND (e = ? OR e IS ?)

This approach is valid, although since the table that I am working with has 16 different columns, I'm hesitant to use 30 conditional statements in my SELECT call.

You might consider using [ISNULL](https://msdn.microsoft.com/en-us/library/ms184325.aspx) instead for a cleaner where clause: `WHERE (ISNULL(a,?) = ?) AND (ISNULL(b,?) = ?)` and so on. — SWalters, Nov 17 '15 at 20:13
In standard SQL (not supported by SQL Server), there's `x IS NOT DISTINCT FROM y`, which is true if `x = y`, except that two `NULL`s compare as equal. See http://stackoverflow.com/questions/10416789/how-to-rewrite-is-distinct-from-and-is-not-distinct-from, especially John Keller's answer there, for how to rewrite that for SQL Server. Note: in your example, it can be written as `WHERE EXISTS(SELECT a, b, c, d, e INTERSECT SELECT ?, ?, ?, ?, ?)`. It scales very well if you add more columns. — , Nov 17 '15 at 20:14
@ScrapingInfinity I think you're assuming that if the parameter is null, then any value of `a` should be accepted. (Or perhaps that if `a` is null, then the row should always be returned no matter what parameter is passed.) That's not what I'm getting from this question. I'm getting the impression that if the parameter is null, then only records `where a is null` should be returned. — , Nov 17 '15 at 20:19
@hvd True, that's a possibility, perhaps some clarification from the OP would help. — SWalters, Nov 17 '15 at 20:21
@hvd That's correct, if I am understanding correctly, yes. I want to see if a row exists in a table prior to inserting it in such a way that NULLs will not break the SELECT statement. — hodgepodge, Nov 17 '15 at 20:22
@hodgepodge Then go with John Keller's answer that I linked to, it does exactly that. :) — , Nov 17 '15 at 20:22

Kittoes0124 · Accepted Answer · 2016-02-04T16:57:29.880

Edit: Recent activity prompted me to reread my accepted answer and I have no idea what I was thinking when I wrote it. Sure, it will work if you're careful but it's not a good solution. Instead, the intersect and except features should be used because they are designed to gracefully handle null comparisons:

declare @a int = null;
declare @b int = null;

select Data.*
from (values
    (1, 1)
  , (1, null)
  , (null, 1)
  , (null, null)
) as Data (a, b)
where exists (
          select @a, @b
          intersect
          select Data.a, Data.b 
      );

I also noticed that @hvd actually shared this solution in the comments so I hope you followed his advice and not mine!

Original (terrible) Answer: I think this does what you're asking for with the caveat that you must be careful about which value you choose as your fallback. The choice of zero here is poor because 0 is potentially a valid value in the table.

declare @i1 int = null;
declare @i2 int = null;

select Data.*
from (values
    (1, 1)
  , (1, null)
  , (null, 1)
  , (null, null)
) as Data (a, b)
where Coalesce(Data.a, 0) = Coalesce(@i1, 0)
  and Coalesce(Data.b, 0) = Coalesce(@i2, 0);

3bh · Answer 2 · 2015-11-17T20:28:51.037

0

If you know there is some impossible value you can replace NULLs with, such as 0 for an autoincrementing column, you can use ISNULL():

WHERE a = ISNULL(@parameter,0) AND ...

But you are safest to be explicit about what you're doing by putting the additional AND clauses in for NULL checking:

WHERE (a = @parameter OR @parameter IS NULL) AND ...

edited Nov 17 '15 at 20:28

answered Nov 17 '15 at 20:21

3bh

806
1
6
12

Hm. I was hoping that I wouldn't have to use 2(n-1) conditionals where (n) is equal to the number of columns in my table. – hodgepodge Nov 17 '15 at 20:23
Such is SQL. Your question isn't clear as to whether you're trying to NULL check the parameters or the values in the table. If its the parameters, you could probably get away with ISNULL(@parameter,0) or whatever your "known impossible" value is. See the update. – 3bh Nov 17 '15 at 20:28

How do you check if a record exists in SQL server 2012 when NULL values are possible?

2 Answers2