NULL Handling

In SQL, NULL represents a missing or unknown value. It is not the same as zero (0) or an empty string (''). Handling NULL values correctly is essential for writing accurate and reliable queries.

---

What is NULL?

• NULL means no value or unknown value.
• Any arithmetic operation with NULL results in NULL.
• Comparisons with NULL require special syntax — you cannot use = or != directly.

---

Checking for NULL

Use IS NULL or IS NOT NULL to test for NULL values.

Examples:

SELECT * FROM employees
WHERE manager_id IS NULL;

SELECT * FROM employees
WHERE department_id IS NOT NULL;

---

NULL and Comparison Operators

Comparisons like = and <> do not work with NULL.

-- This won't work as expected:
SELECT * FROM employees WHERE manager_id = NULL;

-- Correct way:
SELECT * FROM employees WHERE manager_id IS NULL;

---

NULL in Conditions

NULL affects logical conditions:

• TRUE AND NULL → NULL
• FALSE OR NULL → NULL
• NOT NULL → NULL

This is why queries must explicitly check for NULL.

---

COALESCE

COALESCE returns the first non-NULL value from a list of expressions.

Example:

SELECT first_name, COALESCE(middle_name, 'N/A') AS middle_name
FROM employees;

• If middle_name is NULL, 'N/A' is returned instead.

---

ISNULL and IFNULL

Different databases have vendor-specific functions for handling NULL:

Function	Database
ISNULL(expression, replacement)	SQL Server
IFNULL(expression, replacement)	MySQL
NVL(expression, replacement)	Oracle
COALESCE(expression1, expression2, ...)	Standard SQL

Example (MySQL):

SELECT IFNULL(phone_number, 'Unknown') FROM customers;

---

NULL with Aggregates

Aggregate functions handle NULL values automatically:

• COUNT(*) counts all rows.
• COUNT(column) counts non-NULL values only.
• SUM, AVG, MAX, and MIN ignore NULL values.

Example:

SELECT COUNT(*) AS total_rows,
       COUNT(manager_id) AS managers_only
FROM employees;

---

ORDER BY and NULL

By default, the placement of NULL values when sorting depends on the database:

• Some treat NULL as the lowest value (NULLS FIRST).
• Others treat NULL as the highest value (NULLS LAST).

You can control this explicitly:

SELECT first_name, hire_date
FROM employees
ORDER BY hire_date NULLS LAST;

---

Summary

Concept	Syntax	Example
Check for NULL	IS NULL	WHERE middle_name IS NULL
Replace NULL	COALESCE	COALESCE(phone, 'N/A')
Vendor-specific	ISNULL, IFNULL, NVL	IFNULL(address, 'Unknown')
Aggregate behavior	Ignores NULLs	AVG(salary)

Handling NULL correctly ensures accurate results and prevents unexpected bugs in your queries.