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?
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
Hi, thank you for the reply!
yes I made a mistake, the answer should be 2.