MySQL How to find Null data in a table

Question

Scenario: I have a table with duplicate data. One of the columns of this table is ddate, if it is empty/null I want to select that row (and remove it). But for some reason, I cannot find the null rows with a direct query.

Issue: When I run the following query (1):

select
    `ddate`, 
    count(1) as `nb`
from instrument_nt
group by `ddate`;

I get the number of rows where ddate is NULL and where it has other values. But when I run query (2):

select count(*) from instrument_nt where `ddate` =  Null;

or

select * from instrument_nt where `ddate` =  NULL;

My query result is either 0 or empty.

Question: What is the difference between those two queries (1 and 2)? How can I properly delete the data that has null/missing dates?

You should do `select * from instrument_nt where \`ddate\` IS NULL;` — Chetan, Apr 12 '19 at 14:21
NULL does not match anything, even even NULL = NULL does not match. — Salman A, Apr 12 '19 at 14:22
`ddate = NULL` is never TRUE. Any comparison to NULL always evaluates to NULL. You must use the `IS` operator. — forpas, Apr 12 '19 at 14:22
Possible duplicate of [MySQL: selecting rows where a column is null](https://stackoverflow.com/questions/3536670/mysql-selecting-rows-where-a-column-is-null) — Yurii Verbytskyi, Apr 12 '19 at 19:05

D-Shih · Accepted Answer · 2019-04-12T14:23:41.687

2

NULL mean unknow it's a value.

If you want to get NULL row you need to use IS NULL instead of eqaul NULL

select count(*) from instrument_nt where `ddate` IS  Null;

What is the difference between those two queries (1 and 2)? How can I properly delete the data that has null/missing dates?

(1)

select count(*) from instrument_nt where `ddate` IS  Null;

you will get the amount ddate is NULL from instrument_nt table.

(2)

select * from instrument_nt where `ddate` IS NULL;

you will get a result set which ddate is NULL;

edited Apr 12 '19 at 14:23

answered Apr 12 '19 at 14:21

D-Shih

44,943
6
31
51

Spot on, I did not know that. Will accept once I can. – DGMS89 Apr 12 '19 at 14:22

score 1 · Answer 2 · answered Apr 12 '19 at 14:24

Every null is defined to be different from every other null. Thus, equality to null is always false. See, for example, here, which describes this so-called "three value problem".

For this third class of value, you want to use IS, as in IS NULL or IS NOT NULL.

score 1 · Answer 3 · answered Apr 12 '19 at 14:30

1

use the keyword IS NULL to check the null values in tables

For example:

select * from instrument_nt where `ddate` IS NULL;

answered Apr 12 '19 at 14:30

Murtaza Hussain

3,851
24
30

score 0 · Answer 4 · answered Apr 12 '19 at 14:22

0

MySQL null checks use the IS operator instead of =.

Your query should look like this: select * from instrument_nt whereddateIS NULL;

answered Apr 12 '19 at 14:22

bLind

47
4

MySQL How to find Null data in a table

4 Answers4