0

I have an app where users can select various options off a page and the behind code builds a SQL query to return the relevant records.

In this case a user has selected:

  • Division != '1'

There is also an automatic restriction, in that the Division of returned records has to be in the UserDivisions, which is a variable that looks like

Division IS NULL OR Division IN ('1','1.1','1.1.1')

So returned records have to be in one of the listed divisions, or have NULL as a value.

My code is currently processing these restrictions separately, so the final SQL looks like

Select top 1000 Division, WoNum, WoType 
From WorkOrder
Where (Division IS NULL OR Division IN ('1','1.1','1.1.1'))
AND (Division <> '1')

This query returns all the records with Division = '1.1' and '1.1.1', and it doesn't return any in Division '1', like it should, but it also doesn't return any records with Division = Null.

How can I build this query to return NULLs as well, and why doesn't the above query return NULLs?

GreySage
  • 1,153
  • 19
  • 39
  • 1
    `and (Division <> '1')` eliminates the nulls. because null can't be evaluated to <> '1' it comes back "undefined" undfined isn't true and gets excluded. – xQbert Apr 04 '19 at 15:37
  • @xQbert Why does it eliminate the nulls? Aren't nulls != '1' too? – GreySage Apr 04 '19 at 15:38
  • 1
    Comparisons to NULLs always fail. That's why `Division IS NULL OR` was needed in the first condition. As it is, `Division <> '1'` will fail for any NULLs – Panagiotis Kanavos Apr 04 '19 at 15:38
  • @GreySage no, `NULL` means nothing. You can't compare nothing with something – Panagiotis Kanavos Apr 04 '19 at 15:38
  • Use: `WHERE ((Division IN ('1','1.1','1.1.1') AND (Division <> '1')) OR Division IS NULL)` – xQbert Apr 04 '19 at 15:39
  • BTW the condition `Division IN ('1','1.1','1.1.1')` is more restrictive than `Division <> '1'`. You might as well remove it and use `Division IN ('1.1','1.1.1')` – Panagiotis Kanavos Apr 04 '19 at 15:40
  • *On the other hand* if you want to find *children* in a hierarchy, a) you should use a `hierarchyid` and b) looks like the query you wanted was `those children of "1" and any children of other root nodes` – Panagiotis Kanavos Apr 04 '19 at 15:42
  • 1
    @PanagiotisKanavos except 1 is in the first set but getting excluded by the division <> 1. better would be `Division in ('1.1','1.1.1')` but I'm gussing that as it's built dynamically its later so include first then exclude dynamic logic – xQbert Apr 04 '19 at 15:42
  • @xQbert I suspect the real intention was to perform a hierarchical query - children of `1` and all other root nodes except `1` – Panagiotis Kanavos Apr 04 '19 at 15:43
  • Thanks for explaining that. Can you write up an answer so I can accept it? – GreySage Apr 04 '19 at 15:43

2 Answers2

1

and (Division <> '1') eliminates the nulls. This is because null can't be evaluated to <> '1'. The RDBMS returns "undefined". Undefined isn't true and gets excluded.

You could do this instead; but it seems odd since you want division 1 then exclude it.

WHERE ((Division IN ('1','1.1','1.1.1') AND (Division <> '1')) OR Division IS NULL)

xQbert
  • 34,733
  • 2
  • 41
  • 62
0

When you compare a NULL to a value it always returns undefined

...which is not true and therefore evaluates as false. So AND (Division <> '1') will remove all NULLs from the return set.

Some ways to do the same query but include the nulls:

  • Where (Division IS NULL OR Division IN ('1','1.1','1.1.1')) AND (Division <> '1' OR Division IS NULL)

  • WHERE ((Division IN ('1','1.1','1.1.1') AND (Division <> '1')) OR Division IS NULL)

  • WHERE (ISNULL(Division,'') <> '1') AND (Division IS NULL OR Division IN ('1','1.1','1.1.1'))

  • WHERE (COALESCE(Division,'') <> '1') AND (Division IS NULL OR Division IN ('1','1.1','1.1.1'))

which is functionally equivalent to

  • WHERE (CASE WHEN Division IS NULL THEN '' ELSE Division END) <> '1' AND (Division IS NULL OR Division IN ('1','1.1','1.1.1'))
GreySage
  • 1,153
  • 19
  • 39
  • Technically it's not false, nor is it true it's undefined. but since the engine looks for true and it's not true then it gets ignored. – xQbert Apr 05 '19 at 18:24