NULL Values in SQL

NULL means that a value is missing, unknown, or not available.

SELECT *
FROM Students
WHERE mobile IS NULL;

This returns students whose mobile number has no value stored.

NULL and zero are completely different values.

SELECT *
FROM Students
WHERE fee = 0;

This finds students whose fee is exactly zero. It does not find students whose fee is NULL.

An empty string '' is different from NULL.

SELECT *
FROM Students
WHERE mobile = '';

This searches for an empty string, not a NULL value.

Use IS NULL when you want to find records containing NULL.

SELECT *
FROM Students
WHERE email IS NULL;

This returns students whose email value is NULL.

Use IS NOT NULL to find records where a value exists.

SELECT *
FROM Students
WHERE email IS NOT NULL;

This returns students whose email column contains a non-NULL value.

SELECT name, mobile
FROM Students
WHERE mobile IS NULL;

This displays the names and mobile numbers of students whose mobile number is missing.

IS NULL is normally used with the WHERE clause.

SELECT *
FROM Students
WHERE address IS NULL;

This finds students whose address has not been stored.

SELECT name, email
FROM Students
WHERE email IS NULL;

This can be useful for finding students whose email addresses need to be collected.

SELECT name, email
FROM Students
WHERE email IS NOT NULL;

This returns students who have an email value stored.

SELECT *
FROM Students
WHERE email IS NULL
AND mobile IS NULL;

This finds students whose email and mobile values are both NULL.

SELECT *
FROM Students
WHERE email IS NULL
OR mobile IS NULL;

This returns students who have either a missing email or a missing mobile number.

A common mistake is using the equality operator with NULL.

Incorrect:

SELECT *
FROM Students
WHERE email = NULL;

Correct:

SELECT *
FROM Students
WHERE email IS NULL;

Use IS NULL to test for NULL values.

Do not use != NULL to check for non-NULL values.

Incorrect:

SELECT *
FROM Students
WHERE email != NULL;

Correct:

SELECT *
FROM Students
WHERE email IS NOT NULL;

COUNT(column_name) counts non-NULL values in that column.

SELECT COUNT(email)
FROM Students;

This counts the students whose email column contains a value.

COUNT(*) counts rows regardless of whether individual columns contain NULL.

SELECT COUNT(*)
FROM Students;

This counts all rows in the Students table.

SELECT name, fee
FROM Students
ORDER BY fee ASC;

When sorting a column containing NULL values, the exact position of NULL depends on the database system and sorting rules.

You can set a column to NULL using UPDATE when the column allows NULL.

UPDATE Students
SET email = NULL
WHERE student_id = 101;

This removes the stored email value for the specified student by setting it to NULL.

You can use COALESCE() to display an alternative value when a column is NULL.

SELECT name,
COALESCE(email, 'Not Available') AS email
FROM Students;

If email is NULL, the query displays "Not Available".

COALESCE returns the first non-NULL expression.

SELECT name,
COALESCE(mobile, alternate_mobile, 'No Number')
AS contact
FROM Students;

The query first checks mobile, then alternate_mobile, and finally displays "No Number" if both are NULL.

Calculations involving NULL generally result in NULL.

SELECT fee, fee + 1000 AS new_fee
FROM Students;

If fee is NULL, the calculated new_fee will also be NULL.

COALESCE can be used when you want to treat NULL as a specific value during a calculation.

SELECT fee,
COALESCE(fee, 0) + 1000 AS new_fee
FROM Students;

Here, a NULL fee is treated as 0 for the calculation.

SELECT city, COUNT(*)
FROM Students
GROUP BY city;

Depending on the database system, rows with NULL city values can form a group representing NULL.

SELECT name, mobile
FROM Students
WHERE address IS NULL;

This is useful for finding student records that need address information.

SELECT *
FROM Students
WHERE name IS NULL
OR mobile IS NULL
OR email IS NULL
OR address IS NULL;

This query finds records where at least one important field is NULL.

SELECT *
FROM Students
WHERE name IS NOT NULL
AND mobile IS NOT NULL
AND email IS NOT NULL
AND address IS NOT NULL;

This returns records where all four specified columns contain non-NULL values.

You can use NOT with an IS NULL condition.

SELECT *
FROM Students
WHERE NOT email IS NULL;

This identifies rows where email is not NULL. The clearer and more common form is:

SELECT *
FROM Students
WHERE email IS NOT NULL;

A LIKE condition does not match a NULL value.

SELECT *
FROM Students
WHERE email LIKE '%gmail.com';

Rows where email is NULL are not returned by this condition.

IN should not be used as a replacement for IS NULL.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi');

Rows where city is NULL do not match this IN condition.

To find NULL cities, use:

SELECT *
FROM Students
WHERE city IS NULL;

Suppose an institute wants to find students who have not provided an email address or mobile number.

SELECT name, mobile, email
FROM Students
WHERE mobile IS NULL
OR email IS NULL;

This query finds student records with missing contact information.

SELECT name, age, course, city, mobile, email
FROM Students
WHERE mobile IS NOT NULL
AND email IS NULL
AND city IS NOT NULL
AND course IN ('Python', 'Java')
ORDER BY name ASC;

This query:

• Finds students who have a mobile number.
• Finds students whose email is NULL.
• Requires the city to be available.
• Allows Python or Java courses.
• Sorts the results alphabetically by name.