🚫 NULL in SQL represents the absence of a value or unknown data. It does not mean zero, an empty string, or the word "NULL". Understanding how SQL handles NULL values is essential because they behave differently from regular values in comparisons, calculations, and queries.

📖 What is a NULL Value?

A NULL value indicates that a column has no value assigned or that the value is currently unknown. For example, a student may not have an email address yet, or an employee's resignation date may not be available.

📊 Sample Table

Consider the following Students table:

🔍 Checking for NULL Values

Since NULL represents an unknown value, it cannot be compared using the standard comparison operators such as = or <>. Instead, SQL provides the IS NULL and IS NOT NULL operators.

1️⃣ Using IS NULL

The IS NULL operator retrieves rows where a column contains a NULL value.

SELECT *
FROM Students
WHERE Email IS NULL;

2️⃣ Using IS NOT NULL

The IS NOT NULL operator retrieves rows where a column contains a valid (non-NULL) value.

SELECT *
FROM Students
WHERE Email IS NOT NULL;

❌ Incorrect Way to Check for NULL

Never use the equality operator to compare with NULL.

-- Incorrect
SELECT *
FROM Students
WHERE Email = NULL;

🧮 NULL Values in Calculations

Arithmetic operations involving NULL usually return NULL because the result of an operation with an unknown value is also unknown.

SELECT
    Salary,
    Bonus,
    Salary + Bonus AS TotalIncome
FROM Employees;

If Bonus is NULL, the value of TotalIncome is typically NULL.

🔄 Replacing NULL Values

Many database systems provide functions to replace NULL with a default value. The function name varies depending on the database.

SELECT
    Name,
    COALESCE(Email, 'Not Available') AS Email
FROM Students;

The COALESCE() function returns the first non-NULL value from its list of arguments and is part of the SQL standard.

📊 NULL Values with Aggregate Functions

Most aggregate functions ignore NULL values.

SELECT
    COUNT(*) AS TotalStudents,
    COUNT(Email) AS StudentsWithEmail
FROM Students;

🔗 NULL Values with ORDER BY

When sorting data, databases may place NULL values either at the beginning or the end of the result set by default. This behavior differs among database systems. Some databases allow explicit control using NULLS FIRST or NULLS LAST.

SELECT Name, Email
FROM Students
ORDER BY Email NULLS LAST;

💼 Real-World Example

Suppose a university wants to contact only students who have provided an email address.

SELECT StudentID,
       Name,
       Email
FROM Students
WHERE Email IS NOT NULL
ORDER BY Name;

This query excludes students whose email address has not yet been recorded.

⚠️ Best Practices

🚀 Key Points to Remember