DOUBT REGARDING SQL- COUNT QUERY
Hello team,
I am in the SELECT STATMENT PART OF SQL COURSE.
I have a doubt regarding the aggregate function functionality.
It is said in the course that the aggregate function ignores null values.
TO Test that I inserted a record OF EMPLOYEE with NULL first_name and random values with appropriate data for other fields.
For doing this I altered the table and first_name now can be null in employees table.
SO
SELECT
COUNT( FIRST_NAME)
FROM employees;
This query returns 300025 record while ORIGINAL DB HAS ONLY 300024 ROWS thus the query counts the record with NULL FIRST_NAME.
So I wanted to know how's this happening if aggregate function ignores records with null values ?
Pls let me know that.
Kind Regards
Yuvraj.
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.