0

I spent quite a bit of time understanding the mismatch of a language that supports nulls and SQL standards where c.col = null returns 0 rows even if c.col has rows with null in it. I began to wonder in what use cases would someone want to do c.col = null in a where clause in SQL.

I do clearly get that currently if I do WHERE c.col is null, I get back rows where c.col contains a null. This begs the question though, there must be a use and reason for c.col = null to have differing behavior. It must be used by someone, right? I am then curious the context of when we do that?

(I see tons of confusion on SO where people --including me-- thought that in SQL WHERE c.col=null would result in all rows that had null in column col). However, I also realize there is contexts I don't know about and am curious of the context when someone might do c.col = null.

thanks, Dean

zealous
  • 7,336
  • 4
  • 16
  • 36
Dean Hiller
  • 19,235
  • 25
  • 129
  • 212
  • The SQL standard insists that you cannot use = NULL, or <> NULL, and instead should use IS NULL or IS NOT NULL. – Abra Jul 17 '20 at 04:43
  • @Abra First, thanks, you are way closer to answering my question then the answer below. So, next, why does the SQL standard insist on never using =NULL ? Is there a blog on the reasoning behind this decision? Why not just alleviate a ton of confusion and define = null to be same as is null or does that some how cause confusion with another crowd? – Dean Hiller Jul 17 '20 at 12:57
  • The "duplicate" question explains why the SQL standard insists on `is null` and not `= null`. Did you read it? Alternatively, see https://www.red-gate.com/hub/product-learning/sql-prompt/the-null-mistake-and-other-sql-null-heresies – Abra Jul 17 '20 at 16:04
  • yeah, I read that too @Abra but it doesn't explain why we don't 'fail' on = null which is probably my last question I guess. You say the spec says you can't use it so why don't databases just fail it(or at least add an option so it fails all of those). Of course, if it's not in use, why not just re-define it to mean what many are expecting/want....well, that would probably be a tough sell as many are about sticking with theory instead of the practicality that would come with = null I guess. oh well. Still curious why not fail? At any rate, thanks! – Dean Hiller Jul 17 '20 at 16:49

1 Answers1

3

NULL is a special value that signifies no value. Comparing a column to NULL using the = operator is undefined.

instead you should use is to get the column which has NULL value as following

select *
from yourTable
where columnName is null

for e.g. let's say you have following table customer and you want to find names NOT referred by the person with referee_id 2.

| name | referee_id|
+------+-----------+
| Will |      NULL |
| Jane |      NULL |
| Alex |         2 |
| Bill |      NULL |
| Zack |         1 |
| Mark |         2 |

your expected output should be

| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |

to get your output correct you also need to consider referee_id with NULL values, so your quesry should be

select
    name
from customer
where referee_id <> 2
or referee_id is null
zealous
  • 7,336
  • 4
  • 16
  • 36
  • sorry, this does not answer my question. I already know about is null. What I don't get is what = null is good for? If it's good for nothing, it should outright fail to let people know. When would I se c.col = null ? – Dean Hiller Jul 17 '20 at 12:55