DOUBT REGARDING SQL- COUNT QUERY

Hi Yuvraj!

Thanks for reaching out.
By creation, the DDL of the employees table does not allow the insertion of null values for any column.

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Have you changed the set up of the table?
Also, which query did you execute to add a null value?
Please reply to these questions and we'll take it from there - I believe using a practical approach to solving this problem will be more beneficial.

Looking forward to your answer.
Best,
Martin

Hi Martin,
Thank you for your reply
I actually changed the structure of the table by following:

ALTER TABLE employees
CHANGE first_name first_name VARCHAR(14) DEFAULT NULL;

For inserting a value I used
INSERT INTO EMPLOYEES(EMP_NO, BIRTH_DATE, LAST_NAME, GENDER, HIRE_DATE) VALUES (1, '2000-01-15', 'DAX','M', '2021-10-25');

N I Finally applied:
SELECT
COUNT(first_name)
FROM employees;

which gave me an output of 300025 which should not be the case if COUNT IGNORES THE NULL VALUES and result has to be 300024 i.e. the original no. of records with no missing values in the first name.

So am curious how did it happen ?
Waiting for your answer.

Thanks.

Hi Yuvraj!

Thanks for your reply.

Actually, it is interesting to understand the

SELECT
    COUNT (first_name)
FROM 
    employees;

returns a different number. It is COUNT(*) that includes the null values, and COUNT(first_name) that doesn't.
Reference:

SELECT
    COUNT(*)
FROM
    employees;

Therefore, can you please carefully check if you have already inserted a different employees, an employee with a different unique employee number? This would mean that if you try inserting a third new employee now, for instance

INSERT INTO EMPLOYEES(EMP_NO, BIRTH_DATE, LAST_NAME, GENDER, HIRE_DATE) VALUES (2, '2000-01-15', 'DAX','M', '2021-10-25');

(where I've only changed the employee number), then COUNT(first_name) and COUNT(*) should work as expected.
Looking forward to your answer.
Best,
Martin

Hi Martin!
Thanks for your kind reply.

Yup you are correct
```
SELECT
COUNT(*)
FROM
employees;
```
includes all the rows along with missing values.

while
```
SELECT
COUNT(first_name)
FROM
employees;
```
ignores the records with null value in the first name. N yup I think 300025 was shown in my side cause I created 2 records one with NULL first_name and other with xyz first_name, that was mistake from my end.

Thank you for solving the query.

Lastly can you pls let me KNOW why does this following query returns 0 records though I inserted DAX.
```
SELECT
*
FROM employees
WHERE first_name = NULL;
```

Kind Regards
Yuvraj.