MySQL How to find Null data in a table

64
April 13, 2019, at 2:40 PM

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?

Answer 1

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;

Answer 2

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.

Answer 3

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

For example:

select * from instrument_nt where `ddate` IS NULL;
Answer 4

MySQL null checks use the IS operator instead of =.

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

READ ALSO
Right design to structurally ensure data consistency

Right design to structurally ensure data consistency

In my current design, I have app_group, student and group_article:

43
How do i implement a persistent connection to a MySQL Database using PHP?

How do i implement a persistent connection to a MySQL Database using PHP?

I need some help with PHP code architecture and best practiceI need to run update on 4000 rows in a MySQL Database

63
How to use GROUP BY + ORDER BY + WHERE together with GET in PHP?

How to use GROUP BY + ORDER BY + WHERE together with GET in PHP?

I try to make a sales report document using php and connect it from mysql databaseHow can I use where + group by + order (mysql query) with PHP get method? I could just use SELECT and WHERE, but how to add GROUP BY and ORDER BY query?

77
R and RMySQL cant write table to MySQL database

R and RMySQL cant write table to MySQL database

I am trying to write my table from R to the MySQL database

70