sql: select where problem with null value

Question

students table:

id          name
0           NULL
1           John

Query:

SELECT * from students WHERE name != "John";

Expected output:

0           NULL

With my test, the result is empty, how i can solve this?

SELECT * from students WHERE name != "John" OR name == NULL;

It should be noted, that for calculation purposes, NULL is treated as a missing value, not as a value itself. As such, when SQL is trying to say whether or not NULL == "John", it has no idea, because the value's 'missing'. As such, if NULL has a particular meaning in your dataset, you will need to specify. Better yet, if you are able to, create a flag in your table to differentiate between missing values and otherwise meaningful NULL values. — Daniel V, Apr 23 '20 at 22:59

GMB · Accepted Answer · 2020-04-23T22:57:58.987

2

Presumably, you want:

SELECT * from students WHERE name <> 'John' or name is null

Assuming that you are running MySQL, as your screen copy suggests, you could also express with the null-safe operator

SELECT * from students WHERE NOT name <=> 'John'

edited Apr 23 '20 at 22:57

answered Apr 23 '20 at 22:56

GMB

zealous · Answer 2 · 2020-04-23T22:56:50.307

0

Try the following, NULL is not value so you cannot compare using =. take a look at DEMO

SELECT * from students WHERE name != 'John' and name is NULL;

This is a good read.

edited Apr 23 '20 at 22:56

answered Apr 23 '20 at 22:50

zealous

but, I want to use `!=`, my purpose is except `john` – Wang Liang Apr 23 '20 at 22:55
@KingStone You can use it, I have updated my answer. – zealous Apr 23 '20 at 22:57

2 Answers2