Last answered:

16 Apr 2024

Posted on:

15 Apr 2024

0

Resolved: about the queries

Select count(salary)
   from salaries
   where salary >= 100000

is similar to 

Select count(*)
   from salaries
   where salary >= 100000


why the 2nd query also counts the records with NULL values when the condition WHERE salary>100000 is already applied? is this how the syntax works ?

id    salary
1    90000
2    110000
3    NULL
4    120000
5    95000

considering this table above

the first query would give 3 as a result
and the 2nd query would give 4 as a result. 
But why?

2 answers ( 2 marked as helpful)
Instructor
Posted on:

15 Apr 2024

0

Hi Agrim!
Thanks for reaching out.


Actually, both of the queries you provided will give the same result under normal circumstances because the WHERE clause excludes any rows where salary is NULL before the counting occurs. NULL values do not meet any relational operator conditions (like >=, <, =, etc.), so they are inherently excluded by the WHERE clause.

For the table you provided, both queries will count only the rows where salary is at least 100,000:

- Row 2: salary = 110000
- Row 4: salary = 120000

Therefore, both queries should return 2, not 3 or 4, as NULL values and any salaries below 100,000 are excluded by the WHERE condition.


Hope this helps.
Best,
Tsvetelin

Posted on:

16 Apr 2024

0

Hi, thank you for the reply!
yes I made a mistake, the answer should be 2.


Submit an answer