SQL conditional where, return all if condition not met

Question

I have a similar question to SQL Conditional Where and Conditional WHERE clause in SQL Server, except when the condition is not met, I want to return all rows (as if there were no WHERE clause).

One solution could be to do the following. Note that <ColLetter> and <ColValue> are string insertions, where <ColLetter> will be one of ColA, ColB, ColC, or NULL, and <ColValue> will be a value that can appear in one of those columns (or is NULL if the <ColLetter> is).

SELECT ID, ColA, ColB, ColC
FROM MainTable t
WHERE (
  -- <ColLetter> is not null, corresponding to a column name in MainTable
  (ISNULL(<ColLetter>, 0) <> 0 AND t.<ColLetter> =  <ColValue>)
  OR
  -- <ColLetter> is null, I want to return all rows
  (ISNULL(<ColLetter>, 0) = 0 AND t.ID is not null)
  )

The issue with this is that it checks the ID column for nulls unnecessarily (there won't be any nulls) and it is a large table. Is there a better way to do this?

Charles Bretana · Answer 1 · 2017-09-11T07:37:33.960

0

where case <colLetter>
     when '<nameOfColA>' then nameOfColA
     when '<nameOfColB>' then nameOfColB
     When '<nameOfColC>' then nameOfColC
     else isNull(<colValue>, 0) end 
      = isNull(<colValue>, 0)

edited Sep 11 '17 at 07:37

answered Sep 11 '17 at 07:34

Charles Bretana

143,358
22
150
216

Unless I am misreading this, I think you have misunderstood. `` will be the name of one of the non-ID columns if it is not null. `` will be a value that can appear in a row of the specified column. – conor Sep 11 '17 at 07:37
ok, yes, I think I get it. But you haven't told us those column names, so I had to use arbitrary names. (You know what these are, right?) Put the actual names in the SQL. – Charles Bretana Sep 11 '17 at 07:40
For the purposes of this question, I have used dummy names but they may as well be the actual names. So there is a column call `ID`, there is a column called `ColA`, a column called `ColB` and a column called `ColC`. In reality there are many more columns and they have proper names (e.g. `Address`). This answer has still misunderstood the aim I think. My apologies if my explanation was poor! – conor Sep 11 '17 at 08:03

score 0 · Answer 2 · answered Sep 11 '17 at 07:44

0

This boils down to

SELECT ID, ColA, ColB, ColC
FROM MainTable t
WHERE (
   -- <ColLetter> is null, I want to return all rows
   ISNULL(<ColLetter>, 0) = 0 
   -- Check the value otherwise
   OR t.<ColLetter> = <ColValue>)
  )

answered Sep 11 '17 at 07:44

Serg

22,285
5
21
48

Thanks Serg. You were very close, but in the case where `` is not null, your version would have returned rows where `t. = ` AND where `` is NULL. I've edited this accordingly (still awaiting peer review) and now it works as it should. – conor Sep 11 '17 at 08:02
I see no edit. If you need to return NULL `t.` in any case, `OR t. IS NULL` should be added apparently. – Serg Sep 11 '17 at 12:39
It looks like the edit was rejected. I've also since realised that both this answer and the edit I made were insufficient for what I was trying to do. I would delete this question, as it is not clear enough and I have solved my issue through external (non-SQL) means as a workaround. SO will not allow me to delete it, but I've had to un-accept this answer. – conor Sep 12 '17 at 00:09

SQL conditional where, return all if condition not met

2 Answers2