0

I am new to MySQL. I created an Excel file, then saved it in CSV notepad format. The file contains some NULL values in age column. Then I imported that table into one of my existing schemas. Now when I am running query as:

SELECT * FROM mavenmovies.sql2 WHERE rep_age IS NULL;

it's not returning any rows. And when I am running query as: 

SELECT * FROM mavenmovies.sql2 WHERE rep_age IS NOT NULL;

it's returning all the rows that include the rows containing NULL values in rep_age. I checked and there is no space in the NULL cells. I don't understand why?

pensum
  • 980
  • 1
  • 11
  • 25
rupam
  • 1
  • 2
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Dec 02 '19 at 20:12
  • 5
    Apparently `rep_age` is `varchar`, and, depending on what you had in the CSV, either an empty string `''` or a string `'NULL'` was imported. See https://stackoverflow.com/a/2675493/11683. – GSerg Dec 02 '19 at 20:13
  • Execute: `SELECT rep_age, length(rep_age) FROM mavenmovies.sql2` to check the length of each value. – forpas Dec 02 '19 at 20:16

1 Answers1

0

You can check by using 

SELECT COALESCE(SIGN(LENGTH(rep_age)),0) as rep_age, COUNT(*) as count
  FROM mavenmovies.sql2 
 GROUP BY COALESCE(SIGN(LENGTH(rep_age)),0)

existence and count of null and non-null values, where if SIGN() function returns 1, then count means number of non-null values of rep_age columns, otherwise for null values.

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55