How does MySQL handle NULL values in the WHERE and HAVING clauses?

Q: How does MySQL handle NULL values in the WHERE and HAVING clauses?

In MySQL, **NULL** represents missing or unknown values. When using **WHERE** and **HAVING** clauses, comparisons with NULL must be handled carefully because standard operators like `=` or `>` do not work as expected with NULL. In WHERE clauses, use `IS NULL` or `IS NOT NULL` to filter rows with NULL values, e.g., `WHERE salary IS NULL`. Comparisons like `salary = NULL` will always return FALSE because NULL is not equal to any value, including itself. HAVING clauses handle NULL in aggregates according to function rules: for example, `SUM(NULL)` ignores NULLs, while `COUNT(column)` counts only non-NULL values. You can combine IS NULL checks with aggregate filters, e.g., `HAVING SUM(salary) IS NOT NULL AND SUM(salary) > 100000`. Proper handling of NULL values ensures that queries return accurate results and avoid unexpected omissions when filtering or aggregating data. SELECT department_id, SUM(salary) AS total_salary FROM employees WHERE hire_date IS NOT NULL GROUP BY department_id HAVING total_salary IS NOT NULL AND total_salary > 200000;

Difficulty: 5/10

NULL handling, WHERE clause, HAVING clause

Handling NULL Values in WHERE and HAVING Clauses in MySQL

In MySQL, NULL represents missing or unknown values. When using WHERE and HAVING clauses, comparisons with NULL must be handled carefully because standard operators like = or > do not work as expected with NULL.

Key Points About NULL Handling

1
In WHERE clauses, use IS NULL or IS NOT NULL to filter rows with NULL values, e.g., WHERE salary IS NULL.
2
Comparisons like salary = NULL will always return FALSE because NULL is not equal to any value, including itself.
3
HAVING clauses handle NULL in aggregates according to function rules: for example, SUM(NULL) ignores NULLs, while counts only non-NULL values.

Proper handling of NULL values ensures that queries return accurate results and avoid unexpected omissions when filtering or aggregating data.

Example of NULL Handling

Scenario Questions

0-2 years experience

Follow-up Questions