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
– Chetan, Commented Apr 12, 2019 at 14:21
NULL does not match anything, even even NULL = NULL does not match. — Salman Arshad
– Salman Arshad, Commented Apr 12, 2019 at 14:22
ddate = NULL is never TRUE. Any comparison to NULL always evaluates to NULL. You must use the IS operator. — forpas
– forpas, Commented Apr 12, 2019 at 14:22
Possible duplicate of MySQL: selecting rows where a column is null — Yurii Verbytskyi
– Yurii Verbytskyi, Commented Apr 12, 2019 at 19:05

D-Shih · Accepted Answer · 2019-04-12 14:23:41Z

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, 2019 at 14:23

answered Apr 12, 2019 at 14:21

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DGMS89 Over a year ago

Spot on, I did not know that. Will accept once I can.

bishop · Accepted Answer · 2019-04-12 14:24:22Z

1

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.

answered Apr 12, 2019 at 14:24

bishop

39.8k14 gold badges116 silver badges150 bronze badges

Comments

Murtaza Hussain · Accepted Answer · 2019-04-12 14:30:51Z

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, 2019 at 14:30

Murtaza Hussain

4,3631 gold badge30 silver badges31 bronze badges

Comments

bLind · Accepted Answer · 2019-04-12 14:22:31Z

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, 2019 at 14:22

bLind

574 bronze badges

Collectives™ on Stack Overflow

MySQL How to find Null data in a table

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related