NOT NULL returning NULL values

Question

When i run the following query i get 12 rows, when i should have 6 (the ones that are not null in 'firsts') any reason why iam getting all 12? Becuase they are future dates they are empty but i wanted to have the table display the dates for whole year on the table. i have used varchar and not int for the firsts which isnt good practice i know.

SELECT * 
FROM office_figures 2016 
WHERE date_figures=2017 AND firsts IS NOT NULL

`firsts` are empty and not `NULL`, you must populate them with `NULL` or set default to `NULL`. — Arman P., Aug 21 '17 at 20:34
when you say populate with null..do you mean actually put 'null' in the cells? — Glen, Aug 21 '17 at 20:51

score 3 · Accepted Answer · answered Aug 21 '17 at 20:34

3

Probably the firsts that you don't want to be there aren't null, they might be just empty strings ''. Try this:

SELECT * 
FROM office_figures 2016 
WHERE date_figures=2017 AND firsts IS NOT NULL AND firsts != ''

answered Aug 21 '17 at 20:34

David Alves

398
5
19

thanks a lot, it worked . i really need to know this now without asking,,can you point me to a reference book that taught you ? – Glen Aug 21 '17 at 20:38
Sorry for the late late late response, I've learnt this from experience, happned to me some times and it was hard to figure it out – David Alves Mar 12 '21 at 14:46

Black Mamba · Answer 2 · 2017-08-21T20:39:17.507

SELECT * 
FROM office_figures 2016 
WHERE date_figures=2017 AND firsts IS NOT NULL AND firsts !="";

You need to check for an empty value in column as well as "" != NULL.

Null is an absence of a value. An empty string is a value, but is just empty. Null is special to a database.
Null has no bounds, it can be used for string, integer, date, etc. fields in a database.
NULL isn't allocated any memory, the string with NUll value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".

Difference between NULL and Blank Value in Mysql
Check the above link to know more.

NOT NULL returning NULL values

2 Answers2