Explain the concept of NULL values in SQL.

In SQL, a NULL value represents missing, unknown, or undefined data. It’s not the same as zero or an empty string — it simply means “no value has been assigned.”

For example, if we have a table employees with columns like name, department, and bonus, some employees might not have received a bonus yet. In that case, the bonus column for those employees would be NULL.

Example:

Here, Bob’s bonus is NULL — meaning it’s not known, not zero.

To check for NULLs, SQL provides special operators:

SELECT name
FROM employees
WHERE bonus IS NULL;

and

SELECT name
FROM employees
WHERE bonus IS NOT NULL;

We cannot use regular comparison operators like = or != with NULL, because NULL represents an unknown — any comparison with NULL results in UNKNOWN.

In one of my projects, I faced a challenge when filtering customer data where the “phone_number” field had NULLs. Using a condition like WHERE phone_number != '' didn’t work as expected, because NULLs were being skipped. The correct approach was WHERE phone_number IS NOT NULL AND phone_number != ''.

Another key point is that aggregations behave differently with NULLs. For example,

SELECT AVG(bonus) FROM employees;

The AVG function ignores NULLs while computing the average — it only considers non-null values.

A limitation of NULLs is that they can make conditions and joins tricky. For example, when joining two tables, if the join key has NULLs, those rows won’t match. This can lead to unexpected missing records unless handled explicitly.

As an alternative, I sometimes use functions like COALESCE() or IFNULL() to replace NULLs with a default value. Example:

SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;

This treats NULL bonuses as 0 for reporting purposes.

In summary — NULL represents “no value”, not zero or blank. Handling it properly is crucial because it affects filtering, comparisons, joins, and aggregations. Recognizing when and how to replace or preserve NULLs is key to accurate query results.