How to ignore NULL in WHERE clause when SELECT is used?

Question

With the following code:

CREATE TABLE #MyTable 
(
    PartNum VARCHAR(20),
    PartColor VARCHAR(100)
)

INSERT INTO #MyTable 
VALUES
    ('00039','Blue'),
    ('00039','Red'),
    ('01234','Blue'),
    ('23456','Red')

CREATE TABLE #Variables 
(
    VarName VARCHAR(20),
    Value   VARCHAR(100)
)

INSERT INTO #Variables VALUES
('PartNum', '00039'),
('PartColor', NULL)

SELECT *
  FROM MyTable
 WHERE PartNum   = (SELECT Value FROM #Variables WHERE VarName = 'PartNum')
   AND PartColor = (SELECT Value FROM #Variables WHERE VarName = 'PartColor')

If PartColor is NULL, that part of the WHERE clause should be ignored and all records should be returned regardless of PartColor (assuming PartNum = 00039)

I know I can do it this way:

DECLARE @PartNum   VARCHAR(20) = '00039'
        @PartColor VARCHAR(100) = NULL

SET     @PartColor = ISNULL(@PartColor, '-1')

SELECT *
  FROM MyTable
 WHERE PartNum   = @PartNum
   AND PartColor IN (SELECT (@PartColor) OR @PartColor = '-1')

However, I was playing around with putting variables in a table and not sure how to achieve the same result.

I tried using this but the query returned 0 results:

AND PartColor IN ((SELECT Value from #Variables where VarName = 'PartColor' 
OR (SELECT ISNULL(Value, '-1') from #Variables where VarName = 'PartColor') = '-1'))

I'm pretty sure I can't check for a value that way

This was an idea but the syntax isn't valid:

AND PartColor IN ((SELECT Value from #Variables where VarName = 'PartColor') 
OR (SELECT Value from #Variables where VarName = 'PartColor') IS NULL)

Why do you want to create a variables table? It's just making your life harder, and the query more complicated. — allmhuran, Sep 01 '21 at 20:26
Possibly using `exists` would be easier, or just join directly to your table. You should include a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) to fully demonstrate. — Stu, Sep 01 '21 at 20:34
I agree this isn't the best design. Maybe present your real problem. On the other hand, what is the schema of your 'MyTable' table. I suspect you are using it the wrong way and having the records on different rows. As others suggested, build a simple example with dbfiddle and we will find you an answer in no time! — Suciu Eus, Sep 01 '21 at 20:41
The problem is that variables aren't persistent across batches: https://stackoverflow.com/questions/937336/is-there-a-way-to-persist-a-variable-across-a-go . I have 2 SELECT statements in a query that share the same variables, and wanted to see if splitting them into batches helped with performance. — Jeff Brady, Sep 01 '21 at 20:59
It won't help performance. in fact, it's likely to make performance worse, especially if you start to put variables which should rightly have different datatypes into a variables table, since doing so means conforming them all to varchar, which will hurt sargability when they're used. The "I know I can do it this way" part of the question does show room for optimization though: just use `where @myVar is null or myCol = @myVar`. If this is executed often, add `option (recompile)` following the [optional parameter pattern](https://stackoverflow.com/a/62838103/7165279) — allmhuran, Sep 01 '21 at 21:09

score 2 · Accepted Answer · answered Sep 01 '21 at 20:41

2

To address your specific issue of dealing with null values, you can simply use isnull

SELECT *
  FROM MyTable
 WHERE PartNum   = (SELECT isnull(Value, PartNum) FROM #Variables WHERE VarName = 'PartNum')
   AND PartColor = (SELECT isnull(Value, PartColor) FROM #Variables WHERE VarName = 'PartColor')

answered Sep 01 '21 at 20:41

Stu

30,392
6
14
33

1

Thank you! That's exactly what I was looking for – Jeff Brady Sep 01 '21 at 20:44

score 0 · Answer 2 · answered Sep 01 '21 at 23:24

First of all,
you should verify that there's a UNIQUE CONSTRAINT or PRIMARY KEY on the column you're querying (in your case column "VarName"), because in your sub-query you're expecting one single result.

An UNIQUE CONSTRAINT or PRIMARY KEY grants you that only one single row is going to be extracted from your sub-query (or no rows at all).

Why is this important?
Because if your sub-query would extract more than 1 row, than an error will occur, stopping the execution of your query and causing an exception on the application that's querying the database. In your case, you may not have this issue, but this is still something you need to learn.

CREATE TABLE #Variables 
(
    VarName VARCHAR(20) PRIMARY KEY,
    Value   VARCHAR(100)
)

Then, to answer your question you could use multiple sub-queries as @Stu already answered https://stackoverflow.com/a/69020348/7327715

But
When you're dealing with bigger databases you may want to increase the readability of your SQL queryies, to do so I suggest to use variables to store the values of your configurations.

This has the advantage to decrease the length of your query and avoid using sub-queries (they can easily become harder to read, edit, fix and generally speaking manage - for example, imagine a team of people where each member keep adding sub-queryies every time a new configuration is needed)


DECLARE @PartNum VARCHAR(100) = (SELECT Value FROM #Variables WHERE VarName = 'PartNum')
DECLARE @PartColor VARCHAR(100) = (SELECT Value FROM #Variables WHERE VarName = 'PartColor')

SELECT *
FROM #MyTable
WHERE (PartNum = @PartNum OR @PartNum IS NULL)
AND (PartColor = @PartColor OR @PartColor IS NULL)

How to ignore NULL in WHERE clause when SELECT is used?

2 Answers2